Macro + protection

[E]

I have just put my first macro in a sheet as a button which filters certain
row. I want to protect the majority of the sheet so that when I send it out
users can can only amend some cells, but be able to use the filter. When I
protect and click my macro button, it tells me I cannot do this with sheet
protection on.
I really want to use the protection as my users are liable to break pretty
spreadsheets, so can you think of a way I can both protect and use my filter
macro?
Thanks.

 

[Ron de Bruin]

Hi E

If you protect your sheet with your filter on you have a few options that you can check and one of them is Filtering
if your Excel version is not to old.

See
http://www.contextures.com/xlautofilter03.html#Protect

 

[Mike H]

Hi,

Unprotect using code, run your filter and reprotect. Be aware though your
sheet even though protected isn't secure. Excel security is really to prevent
accidental dletions/changes.

Private Sub CommandButton1_Click()
ActiveSheet.Unprotect Password:="Mypass"
' do things
ActiveSheet.Protect Password:="Mypass"

End Sub


Mike

 

[E]

PS I'm using Office 2007.

 

[RadarEye]

Hi E,

It is quite simple.
In your macro:
as first line of code use: ActiveSheet.Unprotect("MyPassword")
as last line of code use: ActiveSheet.Protect

If the Project window is not visible hit [Ctrl]+[R]
Next rightclick in the project window on the project you are editing
Select VBAProject properties
Goto tab "Protection"
Check "Lock project for viewing"
Enter and Confirm a password, ideally an other password as used for
protecting the sheet.

HTH,

Wouter

 

[E]

I need users to be able to filter and unfilter when they want. I unprotected,
filtered then re-protected, but then could not unfilter.

 

[E]

I have never used code before. I typed what you said and it appeared to work
because I could filter with protection on. But when I clicked 'unprotect' it
unprotected without asking me for a password!

 

[Ron de Bruin]

No need for code, see my reply

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm

I have never used code before. I typed what you said and it appeared to work
because I could filter with protection on. But when I clicked 'unprotect' it
unprotected without asking me for a password!

Hi E,

It is quite simple.
In your macro:
as first line of code use: ActiveSheet.Unprotect("MyPassword")
as last line of code use: ActiveSheet.Protect

If the Project window is not visible hit [Ctrl]+[R]
Next rightclick in the project window on the project you are editing
Select VBAProject properties
Goto tab "Protection"
Check "Lock project for viewing"
Enter and Confirm a password, ideally an other password as used for
protecting the sheet.

HTH,

Wouter

 

[E]

Unfortunately your reply does not allow for my corresponding unfliter macro.

No need for code, see my reply

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm

I have never used code before. I typed what you said and it appeared to work
because I could filter with protection on. But when I clicked 'unprotect' it
unprotected without asking me for a password!

Hi E,

It is quite simple.
In your macro:
as first line of code use: ActiveSheet.Unprotect("MyPassword")
as last line of code use: ActiveSheet.Protect

If the Project window is not visible hit [Ctrl]+[R]
Next rightclick in the project window on the project you are editing
Select VBAProject properties
Goto tab "Protection"
Check "Lock project for viewing"
Enter and Confirm a password, ideally an other password as used for
protecting the sheet.

HTH,

Wouter