Bypassing protection for macros applied to an image

[damiand_1982]

G'day guys,

I have an excel worksheet with protection to mainly hide formulas and
prevent users from accidentally deleting/modifying the formulas.

I have created a couple of macro's to reset a filter on a collum and
reset the wrap text function on the sheet, however, when I go to
execute the macro the sheet come up with an error due to it being
protected.

Does anyone know how I can keep the sheet protected (or at least the
formulas hidden) and still have the Macros?

Any help would be appreciated.

Cheers
Damian

 

[NickHK]

You have a couple of options:
- Wrap you code in WS.Unprotect/.Protect statements
- Check out the 5th argument to .Protect .

NickHK

"damiand_1982" <[email protected]>
wrote in message
news:[email protected]...

 

[damiand_1982]

hi nick,

hanks for your quick reply. I created the macro using the record macro
function in excel. To Wrap the code in WS.Unprotect/.Protect
statements, will I need to Alt F11 and go to the VBA script for that
macro?

Cheers
Damian

 

[NickHK]

Yes, as you will need to edit the original code.

Nick

"damiand_1982" <[email protected]>
wrote in message
news:[email protected]...

 

[damiand_1982]

Hi Nick,

I am an excel novice (especially when it comes to VBA script). Could
you look at the code below and give me some advice on where the
protect/unprotect script should be placed?

Sub RefreshScreen()
'
' RefreshScreen Macro
' Macro recorded 15/08/2006 by Damian Dancer
'
' Keyboard Shortcut: Ctrl+r
'
Selection.AutoFilter Field:=5
Cells.Select
Cells.EntireRow.AutoFit
Selection.AutoFilter Field:=5, Criteria1:="Blown Bulbs"
Range("A1").Select
End Sub

Your helps appreciated

 

[Gord Dibben]

Not Nick but will try to help.

Sub RefreshScreen()
'
' RefreshScreen Macro
' Macro recorded 15/08/2006 by Damian Dancer
'
' Keyboard Shortcut: Ctrl+r
'
ActiveSheet.Unprotect Password:="justme"
Selection.AutoFilter Field:=5
Cells.Select
Cells.EntireRow.AutoFit
Selection.AutoFilter Field:=5, Criteria1:="Blown Bulbs"
'more code here to deal with "Blown Bulbs"?
Range("A1").Select
'Selection.AutoFilter to turn off Filter?
ActiveSheet.Protect Password:="justme", DrawingObjects:=True, _
Contents:=True, Scenarios:=True
End Sub


Gord Dibben MS Excel MVP

 

[damiand_1982]

Thanks Gord,

It looks like everything's worked except I need the protection (when
re-enabled by the macro) to still allow the use of autofilters. I have
them setup at the header row. Can you advise whether this can be
coded?

Also in the macro coding you have the questions:

'more code here to deal with "Blown Bulbs"? and
'Selection.AutoFilter to turn off Filter?

What does this mean?

Sorry mate. This is all a big learning process for me. I really
appreciate your help and advice.

Cheers
Damian

 

[damiand_1982]

Thanks Gord,

It looks like everything's worked except I need the protection (when
re-enabled by the macro) to still allow the use of autofilters. I have
them setup at the header row. Can you advise whether this can be
coded?

Also in the macro coding you have the questions:

'more code here to deal with "Blown Bulbs"? and
'Selection.AutoFilter to turn off Filter?

What do you mean by this?

Sorry mate. This is all a big learning process for me. I really
appreciate your help and advice.

Cheers
Damian