macro and protected sheets

[nparsons75]

Hi, I have a worksheet which I protect with these settings.

• Select unlocked cells
• Use auto filter
• Use pivot table and pivot chart

Once set, all works well, however....

I have a macro I use which expands or retracts the row heights in my table. When I run this I use this code.

Sub EXPAND()'
' EXPAND Macro
'


'
[COLOR=#ff0000]ActiveSheet.Unprotect Password:="password"[/COLOR]
    Rows("19:19").Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Rows.AutoFit
    ActiveWindow.SmallScroll Down:=-15
    Range("A1").Select
[COLOR=#ff0000]    ActiveSheet.Protect Password:="password"[/COLOR]
End Sub
Sub RETRACT()
'
' RETRACT Macro
'


'
[COLOR=#ff0000]ActiveSheet.Unprotect Password:="password"[/COLOR]
    Rows("19:19").Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.RowHeight = 16
    Range("J15").Select
    ActiveWindow.SmallScroll Down:=-16
    Range("A1").Select
[COLOR=#ff0000]    ActiveSheet.Protect Password:="password"[/COLOR]
End Sub

I have to activate and deactivate the password for the macro to work.

Heres the problem....

When I do this, the protect sheet selections are all erased...

These no longer apply...

• Select unlocked cells
• Use auto filter
• Use pivot table and pivot chart

Is there a way of retaining these settings either within or outside of the macr?

Thanks.

 

[Fluff]

Record a macro of you unprotecting & then re-protecting the sheet.
That should show you what parameters you need for to use when setting protection