I have a macro that is used to clear a form for future use, i.e it selects a
range and deletes it. Some users were experiencing problems using this when
the sheet was protected so I wrote in a line at the beginning and end to
unprotect and then re-protect the sheet. This works fine.

What I want to know is, is it possible to do this but have the sheet
re-protected with a password? I would like to protect with a password to stop
more competent users unprotecting and altering the sheet. I did this but when
the macro unprotects and reprotects it just protects as normal with no
password so this is no good.

The code is:

Sub ClearForm1()
'
' ClearForm1 Macro
' Macro recorded 18/08/2008 by leavt
'

'
ActiveSheet.Unprotect
Range("M37:N38").Select
Selection.Copy
Range("M35:N36").Select
Selection.PasteSpecial Paste:=xlPasteAllExceptBorders,
Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Range("C2,C8:C11,E8:E11,B12:E13,B14:E15,C16:C31,E1 6:E31,F8:N31").Select
Range("F8").Activate
Range( _

"C2,C8:C11,E8:E11,B12:E13,B14:E15,C16:C31,E16:E31, F8:N31,A40:N41,M37:N38,M35:N36" _
).Select
Range("M35").Activate
Selection.ClearContents
Range("C2").Select
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
End Sub



Thanks in advance.

Specify the password on the .unprotect and .protect lines:

ActiveSheet.Unprotect password:="topsecret"
'lots of stuff
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, _
Scenarios:=True, password:="topsecret"



Pyrite wrote:

I have a macro that is used to clear a form for future use, i.e it selects a
range and deletes it. Some users were experiencing problems using this when
the sheet was protected so I wrote in a line at the beginning and end to
unprotect and then re-protect the sheet. This works fine.

What I want to know is, is it possible to do this but have the sheet
re-protected with a password? I would like to protect with a password to stop
more competent users unprotecting and altering the sheet. I did this but when
the macro unprotects and reprotects it just protects as normal with no
password so this is no good.

The code is:

Sub ClearForm1()
'
' ClearForm1 Macro
' Macro recorded 18/08/2008 by leavt
'

'
ActiveSheet.Unprotect
Range("M37:N38").Select
Selection.Copy
Range("M35:N36").Select
Selection.PasteSpecial Paste:=xlPasteAllExceptBorders,
Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Range("C2,C8:C11,E8:E11,B12:E13,B14:E15,C16:C31,E1 6:E31,F8:N31").Select
Range("F8").Activate
Range( _

"C2,C8:C11,E8:E11,B12:E13,B14:E15,C16:C31,E16:E31, F8:N31,A40:N41,M37:N38,M35:N36" _
).Select
Range("M35").Activate
Selection.ClearContents
Range("C2").Select
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
End Sub

Thanks in advance.

--

Dave Peterson