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]...
>
> 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
>
>
> --
> damiand_1982
> ------------------------------------------------------------------------
> damiand_1982's Profile:
http://www.excelforum.com/member.php...o&userid=37461
> View this thread: http://www.excelforum.com/showthread...hreadid=571224
>

[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]...
>
> 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
>
>
> --
> damiand_1982
> ------------------------------------------------------------------------
> damiand_1982's Profile:
http://www.excelforum.com/member.php...o&userid=37461
> View this thread: http://www.excelforum.com/showthread...hreadid=571224
>

[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

On Mon, 14 Aug 2006 19:20:05 -0400, damiand_1982
<[email protected]> wrote:

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

[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