protecting/unprotecting worksheets in XL97 vba

Hi. I need help!

I'm using a userfrom to place data onto a worksheet, and want to make sure that the worksheet cannot be changed by a user.

If I have the worksheet protected, I can't place the info gathered in the userform onto the worksheet (gives me an error telling me to unprotect the sheet first).

I have tried using the Activesheet.UnProtect method, but keep on getting an error. The error is:

"Run-Time Error 1004"
"Unprotect method of worksheet class failed"

Can anybody help me? I use a click of an OK button to hold all the coding for placing the info onto the worksheet.

When the button is pressed, I want to unprotect the sheet, enter the data, then reprotect the sheet.

Thanks,
Corey D.

[NateO]

Try defining your worksheet differently. E.g.,

Worksheets(1).Unprotect

Hope this helps. Cheers,

Nate

[ This Message was edited by: NateO on 2002-03-13 12:36 ]

Hmmmm. That doesn't seem to help. This is my excat coding:

Application.EnableEvents = False
Application.DisplayAlerts = False
Worksheets(1).Unprotect earth
Application.EnableEvents = True
Application.DisplayAlerts = True

' Coding to paste info goes here

Application.EnableEvents = False
Worksheets(1).Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, Password:="admin"
Application.EnableEvents = True

I get the same error regardless of whether I use activesheet or worksheet(1).

Anyone other ideas? Anyone?

Thanks,
Corey D

[NateO]

Try selecting the sheets first, i.e.:

Application.EnableEvents = False
Application.DisplayAlerts = False
worksheets(1).select
Worksheets(1).Unprotect earth
Application.EnableEvents = True
Application.DisplayAlerts = True

' Coding to paste info goes here

Application.EnableEvents = False
worksheets(1).select
Worksheets(1).Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, Password:="admin"
Application.EnableEvents = True


HTH. Cheers, Nate

[tk19]

You should be able to get away with just using:

Sheets("Mysheet").Unprotect ("admin")

then later,
Sheets("Mysheet").Protect ("admin")

assuming admin is the password.

[nisht]

Friend,
Excel will generate a error if you try to
protect sheet for a protected sheet and same is true for unprotection.
sub prot()
on error resume next
Sheets("Mysheet").Unprotect ("admin")

on error resume next
Sheets("Mysheet").Protect ("admin")

end sub

you can also trap your error and perform the code or convey customise messages

assuming admin is the password.

[Ivan F Moala]

In excel97 ActiveX buttons try changing
the commandbuttons [takefocus*******]
property to False.



Ivan