Strange issue with VBA for protection and filtering - VBA need to be in THISWORKBOOK???

[dazbear]

I have a macro which runs through certain sheets and filters them, which then updates a dashboard.

This works fine if the protection coding sits in the THISWORKBOOK module, however really slows the loading of the whole file.

I tried setting up the same code as a separate macros which runs as a one to protect each sheet. However when I next open the workbook the filter buttons fail. When I put it back into THISWORKBOOK all works again.

I would rather this didn't have to run every time on opening. Any ideas?

Please Login or Register  to view this content.

[TMS]

UserInterFaceOnly:=True is not remembered/retained between sessions. Consequently you will HAVE to have it as part of the Workbook Open event handler.

The only alternative is to Unprotect and Protect the worksheets on an ad hoc basis. Must admit, that's the way that I tend to do it anyway.

Regards, TMS

[dazbear]

Does UserInterFaceOnly:=True have to be set per worksheet or can it be set for a whole workbook on opening?

[TMS]

Each worksheet ... it's part of the protection options for the sheet, not the workbook.

[dazbear]

Thanks for you reply

[TMS]

You're welcome.



If you are satisfied with the solution(s) provided, please mark your thread as Solved.


New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save


You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.