Results 1 to 10 of 10
  1. #1
    Infoprop is offline Novice
    Windows 10 Access 2019
    Join Date
    Mar 2023
    Location
    UK
    Posts
    2

    Make excel row as filter in VBA

    I have created an Excel spreadsheet from an Access table using the excel.Application object in VBA and all I want to do is make a single row have the drop down filters.



    If you were in an Excel spreadsheet you would simply select the row and click on the "Filter" option under "Sort and filter".

    If I wanted to make the row grey then i would simply do something like

    oExcelWrSht.Range("A12:J12").Interior.ColorIndex = 15

    So i am hoping it will be something similar. i.e. declare the range and use a property to make all the cells have a drop down filter.

    Any thoughts anyone?

  2. #2
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,861
    Yes. Use the macro record button and adjust the code to suit.

    That is how I do it.
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  3. #3
    Chris Wright is offline Novice
    Windows 11 Access 2019
    Join Date
    Mar 2023
    Posts
    3
    Hi
    You replied to my post re Access Reports help but I have only just been able to get back on the forum so maybe when you get time we could email each other regards Chris.

    Note:
    email address removed as requested./orange
    Last edited by orange; 03-27-2023 at 10:21 AM.

  4. #4
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,861
    Do not post your email address on the website:
    That will get picked up by scapers.

    Edit your post and remove it. Just allow PMs on this site.

    Plus, please do not hijack other poster's threads?
    You coud have posted this on your own thread?
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  5. #5
    Chris Wright is offline Novice
    Windows 11 Access 2019
    Join Date
    Mar 2023
    Posts
    3
    Love to do as you say but no idea how to edit or nwhat hijacking threads is. MAybe i'll just try unsubscribing

  6. #6
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,861
    Quote Originally Posted by Chris Wright View Post
    Love to do as you say but no idea how to edit or nwhat hijacking threads is. MAybe i'll just try unsubscribing
    OK, Edit option is missing for some reason? Ask the mods to remove it.?
    Hijacking a thread is when you jump in to a thread that has no relevance to that thread.?
    You could have just as easily posted that message in the thread you started, which you mentioned in that post?

    I'll report this thread and ask that the email address is removed, as I cannot now see how to edit. Getting mixed up with another forum I expect.
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  7. #7
    orange's Avatar
    orange is online now Moderator
    Windows 10 Office 365
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Note: As requested: Email address removed in Post #3
    by Orange 27-Mar-23

  8. #8
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,861
    Thank you Jack.
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  9. #9
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,861
    There is an edit option, but presumably removed after a set time. My previous post today is still editable at the moment.
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  10. #10
    Infoprop is offline Novice
    Windows 10 Access 2019
    Join Date
    Mar 2023
    Location
    UK
    Posts
    2

    That worked absolutely wonderfully

    Quote Originally Posted by Welshgasman View Post
    Yes. Use the macro record button and adjust the code to suit.

    That is how I do it.
    That worked wonderfully - why didn't i think of doing it that way.

    So create the macro called ("FilterMacro") in your template as an xlsm file.

    In the Access VBA code just use something like objExcel.Application.Run "FilterMacro"

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Buttons to filter records and make a report
    By Giulio Benvenuti in forum Access
    Replies: 5
    Last Post: 03-19-2021, 07:35 AM
  2. Replies: 1
    Last Post: 04-10-2017, 10:06 AM
  3. Replies: 5
    Last Post: 12-01-2016, 02:22 PM
  4. Replies: 15
    Last Post: 03-28-2014, 07:29 AM
  5. Replies: 1
    Last Post: 10-05-2012, 08:48 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums