Macro to filter and protect sheet with password

Hello,

I'm a VBA novice so apologies for - what I'm sure is - very poor quality of code!

I have a protected sheet, everything will be locked other than selecting cells and using auto-filter. There's a simple macro attached to a text box to remove filter results. After a bit of googling I figured how to keep the filter active after the macro runs - however I can't figure how to keep/reinstate the password protection again after the macro runs.

I've got something like this:

Please Login or Register  to view this content.

So it does what I want, except the unprotect option loses it's password after running macro.

Any help would be appreciated!

Gregg

Hi, Gregg,

have you tried setting up the protection with both parameters (UserInterfaceOnly as well as Password) in one line and as one command?

Ciao,
Holger

Hi Holger,

Thank you very much - that seems to have done the trick. It always seems so simple when somebody points it out! I'll keep on practicing

Please Login or Register  to view this content.

Gregg

Hi, Gregg,

glad itf that solved the problem.

Let me point out another thing in your code. You have ActiveSheet at the start of every line which you may use a With-Statemnet for as well. Please mind that as long as you do not refer to any sheet the ranges you rely on are located on the ActiveSheet (this will come in if you hvae the criteria on a different sheet or are not on the sheet you want to rely on). Maybe you could use somehting like

Please Login or Register  to view this content.

Here you need not be on the TestSheet to run the code (nor on Data Crunch). If you use your version and are on a Sheet called MyChoice anything would be talking place and being related to that sheet, if you want to take the code supplied here and the criteria is on the same sheet just preceed the Range(...) with a dot.

HTH,
Holger

Okay, thank you for the advice. Just started with VBA a couple of months ago - it's very interesting, but also very challenging. Maybe I'll be an expert some day too
I will practice with this code and refer to for future use.

Many thanks,
Gregg