Sheet Protection & Macros

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

breadvan579 - 11 Oct 2004 19:57 GMT

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

Reply to this Message

Paul B - 11 Oct 2004 20:31 GMT

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

Signature

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

> Hi, as a new user please forgive me if I do not provide sufficient
> information/background to my problem which I hope somebody can help
[quoted text clipped - 15 lines]
>
> Would appreciate advice. Thanks

Reply to this Message

Jack Schitt - 11 Oct 2004 20:36 GMT

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"

Signature

Return email address is not as DEEP as it appears

>
> Hi, as a new user please forgive me if I do not provide sufficient
[quoted text clipped - 16 lines]
>
> Would appreciate advice. Thanks

Reply to this Message

Bill Kuunders - 11 Oct 2004 20:42 GMT

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

Reply to this Message