protecting/unprotecting worksheets in XL97 vba

G

Guest

Guest
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.
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
In excel97 ActiveX buttons try changing
the commandbuttons [takefocusonclick]
property to False.



Ivan
 
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,150
Members
448,552
Latest member
WORKINGWITHNOLEADER

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top