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

NickHK

"damiand_1982"
wrote in message
news:damiand_1982.2ci4rt_1155511515.5275@excelforu m-nospam.com...

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

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

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

Nick

"damiand_1982"
wrote in message
news:damiand_1982.2cilfp_1155533108.3186@excelforu m-nospam.com...

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

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

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

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

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


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