Hide columns in macro while protected

[Guest]

I have a macro that hides columns and another that reveals these columns. I
want to have parts of my worksheet protected. When having the worksheet
protected I cannot use the macro. I know that you can write:

ActiveSheet.Unprotect Password:="MyPassword"
Columns("D:G").Select
Selection.EntireColumn.Hidden = True
ActiveSheet.Protect Password:="MyPassword"

This works. The problem is if the user that has access to the password wants
to change the password but is not informed of the fact the macro has to be
changed. Is there any choice you can make when protecting the worksheet so
that columns can be hidden/revealed? They are after all not altered? I am
very greatful for any assistance

 

[NickHK]

Look at the 5th argument to the .Protect method.
Your code will still run on a protect WS.

NickHK

 

[Guest]

thank you! I am really bad at vba so I dont really know how to work this
out...Can you please help me by being a bit more specific..Are you supposed
to set the 5th argument false..How do you write this? I am sorry i am just
completely wothless at this. Thank you very much for your help!

"NickHK" skrev:

 

[NickHK]

Put the cursor in the .Protect and hit the function key F1. The Help will
explain it to you, along with the other arguments.

NickHk

 

[Dave Peterson]

Nick was suggesting that you protect the sheet using the Userinterfaceonly
keyword.

Option Explicit
Sub auto_open()
With Worksheets("sheet1")
.Protect Password:="hi", userinterfaceonly:=True
End With
End Sub

It needs to be reset each time you open the workbook. (excel doesn't remember
it after closing the workbook.)

But if you're using xl2002 or higher, then you'll have to supply the password to
do this. (If I recall correctly, excel would allow you to change this setting
in xl97 and xl2k without knowing the password. But that changed with a newer
version.)

I think I'd yell at that person to not change the password!