Macro + protection

E

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.
 
M

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
 
R

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

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

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

Ron de Bruin

No need for code, see my reply

--

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


E said:
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!

RadarEye said:
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

E

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

Ron de Bruin said:
No need for code, see my reply

--

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


E said:
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!

RadarEye said:
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top