VBA Macro With User Protection Settings

[hovious3]

Hello, all.

I have a protected spreadsheet that uses auto-filter drop downs and a
set of allowed user settings. I needed a macro to unprotect the
sheet, reset all the filter fields, then protect the sheet again. I
was successful in entering code to accomplish all this using
"Auto_Open" and "ActiveSheet.Unprotect Password:="yaddayadda".

However, when the sheet is protected again, the additional allowable
user settings like insert rows, sort, use auto filter, etc. that was
setup in the original sheet are wiped out.

Is there a way to force the allowable user settings within the same
macro? Thank you for your help!

 

[Jim Cone]

From the Help file in XL 2002 for the Protect Method...

ActiveSheet.Protect(Password, DrawingObjects, Contents, Scenarios, _
UserInterfaceOnly, AllowFormattingCells, AllowFormattingColumns, _
AllowFormattingRows, AllowInsertingColumns, AllowInsertingRows, _
AllowInsertingHyperlinks, AllowDeletingColumns, AllowDeletingRows, _
AllowSorting, AllowFiltering, AllowUsingPivotTables)
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)


<[email protected]>
wrote in message
Hello, all.
I have a protected spreadsheet that uses auto-filter drop downs and a
set of allowed user settings. I needed a macro to unprotect the
sheet, reset all the filter fields, then protect the sheet again. I
was successful in entering code to accomplish all this using
"Auto_Open" and "ActiveSheet.Unprotect Password:="yaddayadda".

However, when the sheet is protected again, the additional allowable
user settings like insert rows, sort, use auto filter, etc. that was
setup in the original sheet are wiped out.

Is there a way to force the allowable user settings within the same
macro? Thank you for your help!