Urgent Help Needed!

pinkpig · Mar 28th, 2002, 06:36 AM

Help! I am running a macro that hides rows with a button click and then hides the same columns with another button click, but I need to protect my worksheet. I have tried unprotecting all the cells included in the hide/unhide macro but it won't run the macro with the protection on. It works fine when not protected.

Why? Can I overcome this?

Tom Schreiner · Mar 28th, 2002, 06:41 AM

Hi
Click Tools, Protection, Unprotect Sheet and hope someone did not enter a password.
Tom

Joe Was · Mar 28th, 2002, 06:42 AM

You need to run an un-lock around your code.

Try using the code below.

put form buttons on the sheet to lock and unlock, the password is hardcoded into the VBA code and hidden. This way the user cannot change the password.

This code gos on the sheet module:

Private Sub Worksheet_Change(ByVal Target As Range)
'
' Macro by Joseph S. Was
'
Application.EnableEvents = False

ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, Password:="admin"
Application.EnableEvents = True
End Sub

This code gos on the module:

Sub myUnLock()
'This code will unprotect the sheet.
Application.EnableEvents = False
Application.DisplayAlerts = False
ActiveSheet.Unprotect ("admin")

'Add optional code here!

Application.EnableEvents = True
Application.DisplayAlerts = True

End Sub

This is a very simple form of this.
If this is the route you want to try, I have some very complex systems?

Note: The password is hard-coded.

The unprotect will automatically be re-protected by the first macro when the unprotect is done. JSW

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Mar 28th, 2002, 06:36 AM	#1
pinkpig New Member Join Date: Mar 2002 Posts: 3	Help! I am running a macro that hides rows with a button click and then hides the same columns with another button click, but I need to protect my worksheet. I have tried unprotecting all the cells included in the hide/unhide macro but it won't run the macro with the protection on. It works fine when not protected. Why? Can I overcome this?

Mar 28th, 2002, 06:41 AM	#2
Tom Schreiner Board Regular Join Date: Mar 2002 Location: Cincinnati, Ohio, USA Posts: 6,824	Hi Click Tools, Protection, Unprotect Sheet and hope someone did not enter a password. Tom

Mar 28th, 2002, 06:42 AM	#3
Joe Was MrExcel MVP Join Date: Feb 2002 Location: Central Florida, USA Posts: 7,541	You need to run an un-lock around your code. Try using the code below. put form buttons on the sheet to lock and unlock, the password is hardcoded into the VBA code and hidden. This way the user cannot change the password. This code gos on the sheet module: Private Sub Worksheet_Change(ByVal Target As Range) ' ' Macro by Joseph S. Was ' Application.EnableEvents = False ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, Password:="admin" Application.EnableEvents = True End Sub This code gos on the module: Sub myUnLock() 'This code will unprotect the sheet. Application.EnableEvents = False Application.DisplayAlerts = False ActiveSheet.Unprotect ("admin") 'Add optional code here! Application.EnableEvents = True Application.DisplayAlerts = True End Sub This is a very simple form of this. If this is the route you want to try, I have some very complex systems? Note: The password is hard-coded. The unprotect will automatically be re-protected by the first macro when the unprotect is done. JSW