Sheet Protection & Macros

[breadvan579]

Hi, as a new user please forgive me if I do not provide sufficien
information/background to my problem which I hope somebody can hel
with.

I am using MS Office 2002 SP3 on a Windows XP Home machine.

I have an simple Excel document where several sheets are passwor
protected and this works fine - including several cell by cel
exceptions.

My problem related to a macro command that is activated by a butto
click - the simple command the macro runs is a the hiding of 3 column
and then the unhiding of those same columns if a separte button i
pressed.

This works fine without the worksheet protection on but an receive a
error message "Run Time Error 1004" with the protection activated.

Would appreciate advice. Thank

 

[Paul B]

breadvan, one way would be to unprotect the sheet, run your code and then
protect the sheet, like this

ActiveSheet.Unprotect

'you code here

ActiveSheet.Protect
--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2000 & 2003
** remove news from my email address to reply by email **

 

[Jack Schitt]

You could try:
in the code behind "ThisWorkbook" (ie not in a general module)
enter

Private Sub Workbook_Open()
Application.ThisWorkbook.Worksheets("Sheet1").Protect_
UserInterfaceOnly:=True
End Sub

Substitute the relevant sheet name for "Sheet1"

 

[Bill Kuunders]

You would need to protect and unprotect the workbook each time you run your
code.

Sub seal()
For Each sheet In Sheets
On Error Resume Next
sheet.Protect ("")
Next
ActiveWorkbook.Protect ("")
Application.StatusBar = ""
End Sub

Sub unseal()
ActiveWorkbook.Unprotect ("")
For Each sheet In Sheets
On Error Resume Next
sheet.Unprotect ("")
Next
Application.StatusBar = "NOT sealed"
End Sub

Use the name of the sub and enter it in your hide sub.

i.e.

hide sub()
unseal
your code........
............
seal
EndSub

You can delete the lines with the application. status bar if you like.
I use that to ensure that I can see whether the sheets are protected or not.

Enter your password between the ("")

Have fun
Regards
Bill K