VBA Help - password protected, need to be able to use filters

[delaney1102]

Hi all, self-taught very basic coding (mostly from this website!!) so any help would be appreciated!
I have a multi-sheet workbook that is used by many members of my department. I have a lot of formulas and conditional formatting on one of the tabs so I have it password protected, but users are able to filter a select area and color code as necessary. One of the macros that's written into this worksheet unprotects the sheet so that a certain column can be formatted to a grey color (to indicate that the information cannot be further updated) and a note added that the tab is "FINAL COPY" and then it gets protected again. The issue I have is that after this macro is done, users sometimes need to be able to go back at a later date and use the filter and to format cells that they were able to prior to running the macro. Is there any way to write into this code that they are still able to filter and format the same cells they were able to before hand?


Worksheets("Input Tab").Protect Password:="PASSWORD", userinterfaceonly:=True

Worksheets("Input Tab").Range("J29:J513").Interior.Color = RGB(166, 166, 166)

Range("FinalCopy").Value = "FINAL COPY"


If I'm not explaining well, please ask for further clarification

 

[MARK858]

Both Autofilter and Formatting can be allowed in the Protect method, see the link below for the various options...

Worksheet.Protect method (Excel)

Office VBA reference topic

docs.microsoft.com

 

[Jeffrey Mahoney]

I think you need to add: AllowFiltering:=True to the protection line. Now, the problem I noticed is that the sheet had to be re-protected on workbook startup or if the VBA was reset for any reason. I once added the protection into the SHEET level module so I knew that the users could use filtering. This may not be true for later versions of Excel.