+ Reply to Thread
Results 1 to 7 of 7

Disabling the Advanced filter function

  1. #1
    Registered User
    Join Date
    11-08-2007
    Posts
    3

    Disabling the Advanced filter function

    Hi all,

    I am working on a spreadsheet that has multiple sets of data that are sorted using a macro based on teh auto filter. After i protect the sheet and disable the autofilter function (as i do not wnat people able to access any information other than what i have sorted for) i have found that if you use the advanced filter you can unsort the entire sheet whether it is protected or not.
    does anyone know a way to disable the advanced filter whether by an option or some sort of VBA script?

    Appreciate the help

  2. #2
    Forum Moderator Paul's Avatar
    Join Date
    02-05-2007
    Location
    Wisconsin
    MS-Off Ver
    2016/365
    Posts
    6,881
    Hello ngk08,

    The Advanced Filter menu can be disabled just like any other menu item using VBA. The following statement will disable that entry:
    Please Login or Register  to view this content.
    Remember to reset it back to True before saving and/or closing the workbook so it is available on other worksheets.

  3. #3
    Registered User
    Join Date
    11-08-2007
    Posts
    3
    Thanks for that pjoaquin!

    so that will turn it off for all workbooks, including new ones?

    and this may be a stupid question but my vba is pretty poor, but where do i put the code in order for it to work?

    Cheers!

  4. #4
    Forum Moderator Paul's Avatar
    Join Date
    02-05-2007
    Location
    Wisconsin
    MS-Off Ver
    2016/365
    Posts
    6,881
    Code like the following will unprotect the sheet, disable the Advanced Filter option, then re-protect the sheet:
    Please Login or Register  to view this content.
    You could put that code into the worksheet's code module that you want protected. (Right-click on that sheet's tab and choose View Code, then paste this code into the white window and close the VB Editor. You can then run the macro by pressing ALT+F8 and selecting 'disableAdvFilter' and clicking Run.)

    The following code would re-activate the Advanced Filter menu option, and could be placed in the ThisWorkbook module. In the VB Editor (press ALT+F11 to get there), double-click on the 'ThisWorkbook' entry in the left-hand column, then paste this code:
    Please Login or Register  to view this content.

  5. #5
    Registered User
    Join Date
    11-08-2007
    Posts
    3
    thats fantastic!

    thanks heap pjoaquin, you solved my problem. Much appreciated

  6. #6
    Registered User
    Join Date
    03-12-2015
    Location
    MSP
    MS-Off Ver
    Mac
    Posts
    3

    Re: Disabling the Advanced filter function

    How would I update this to work with Office 365? Or should this work with all versions of Excel?
    Here is the exact code entry I used in VBA, but the macro does not work.

    Sub DisableFiltering()

    'Macro to disable filtering function for collaborators


    Application.CommandBars("Data").Controls("Filter").Controls("Advanced Filter...").Enabled = False


    End Sub

  7. #7
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,068

    Re: Disabling the Advanced filter function

    Coltrabagar,
    Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

    If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

    Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.
    Ben Van Johnson

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1