Results 1 to 6 of 6
  1. #1
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,820
    Thanks
    133
    Thanked 481 Times in 458 Posts

    Autofilter with Protection (xl2000)

    I have a sheet protected with a password but can't seem to get the Autofilter to work.

    I am using
    Activesheet.EnableAutoFilter = True
    Activesheet.Protect password:= zzz, contents:=True, userInterfaceOnly:=True

    (where zzz is a variable string containing the sheet password.

    What am I not doing right?

    zeddy

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Autofilter with Protection (xl2000)

    Did you turn autofilter on before protecting the sheet?

    The code must also run whenever the workbook is opened.
    Steve

  3. #3
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,820
    Thanks
    133
    Thanked 481 Times in 458 Posts

    Re: Autofilter with Protection (xl2000)

    Do I need to?

    zeddy

  4. #4
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,820
    Thanks
    133
    Thanked 481 Times in 458 Posts

    Re: Autofilter with Protection (xl2000)

    I have a button on the sheet to allow a User to toggle Autofilter on and off.
    This works fine without sheet protection.
    But I now need to protect certain ranges from User input.
    The sheet could therefore be in protected mode without the Autofilter on.

    zeddy

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Autofilter with Protection (xl2000)

    EnableAutoFilter does not add the autofilter arrows. It only allows them to be used when the userinterfaceonly is true.

    You must turn on the autofilters first. Something like this to add them

    <pre>Activesheet.Unprotect password:= zzz
    if not ActiveSheet.AutoFilterMode then Range("a1").Autofilter
    Activesheet.EnableAutoFilter = True
    Activesheet.Protect password:= zzz, contents:=True, userInterfaceOnly:=True</pre>


    To remove the autofilters:
    <pre>Activesheet.Unprotect password:= zzz
    ActiveSheet.AutoFilterMode =false
    Activesheet.Protect password:= zzz, contents:=True, userInterfaceOnly:=True</pre>


    I think you can add them with userinterfaceonly but you can not remove them. I just assumed you had to unprotect in either case.
    Steve

  6. #6
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,820
    Thanks
    133
    Thanked 481 Times in 458 Posts

    Re: Autofilter with Protection (xl2000)

    Steve

    Many thanks for your response.
    After reading your last post I went back and loaded the file to try your suggestions.
    But they now seem to work!
    I will amend the code anyway to reflect your suggestions.
    They make a lot of sense.

    Once again many thanks.
    I really appreciate it!

    zeddy

Posting Permissions

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