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.

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




--

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


"E" wrote in message ...
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.

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" wrote:

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.

PS I'm using Office 2007.

"E" wrote:

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.

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

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

"Mike H" wrote:

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" wrote:

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.

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" wrote:

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

No need for code, see my reply

--

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


"E" wrote in message ...
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" wrote:

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

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

"Ron de Bruin" wrote:

No need for code, see my reply

--

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


"E" wrote in message ...
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" wrote:

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