Hide columns in macro while protected

[Sally Mae]

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

"Sally Mae" <[email protected]> wrote in message
news:[email protected]...
> 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

[Sally Mae]

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:

> Look at the 5th argument to the .Protect method.
> Your code will still run on a protect WS.
>
> NickHK
>
> "Sally Mae" <[email protected]> wrote in message
> news:[email protected]...
> > 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]

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

"Sally Mae" <[email protected]> wrote in message
news:[email protected]...
> 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:
>
> > Look at the 5th argument to the .Protect method.
> > Your code will still run on a protect WS.
> >
> > NickHK
> >
> > "Sally Mae" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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
> >
> >
> >

[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!



Sally Mae wrote:
>
> 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

--

Dave Peterson