Runtime error 57121

[Henk]

To unprotect my entire workbook on opening, I start on ThisWorkbook the
following procedure :

Private Sub WorkBook_Open()
Application.ScreenUpdating = False

For Each WkSht in Worksheets
WkSht.Unprotect Password:="mypassword"
Next WkSht

I have a lot of workbooks with this procedure fuctioning properly, however
there is one which each time results in Runtime error 57121 :
Applikcation-defined or object-defined error. Debugging points me to the line
: WkSht.Unprotect Password:="mypassword"

(In the same workbook I have a macro, using the same code, wich is working
without errors.)

Any idea how to solve this?

 

[paul.robinson]

Hi
Does that sheet have that password? Check there isn't a leading or
trailing space in the sheet password.

regrds
Paul

 

[Henk]

Thanks for your quick answer Paul, but the password is the same on all sheets
of my workbook.

I deleted the entire Sub Workbook_open() and reinserted the simple procedure :

Sub Workbook_open()

Sheets("Sheet1").Unprotect Password:="mypassword"

End Sub

I got the same error when I opened the workbook.

Regards,

Henk

 

[paul.robinson]

Hi
I've tested this with sheet not visible, workbook protected, two
workbooks open & so on, and it works OK.
Only things I can think of,
1. Workbook is corrupted in some way

2. Sheet does not exist? In which case you would need
On Error Resume Next
Set myWS = Sheets(mySheet)
On Error GoTo 0

to test sheet exists before trying to unprotect it.
3. I searched the knowledge base, and there seems to be vague
suggestions that creating the workbook on a mac then using on a PC
might create this error.

sorry can't be of more help
regards
Paul

 

[Dave Peterson]

You have a response at your other post.

To unprotect my entire workbook on opening, I start on ThisWorkbook the
following procedure :

Private Sub WorkBook_Open()
Application.ScreenUpdating = False

For Each WkSht in Worksheets
WkSht.Unprotect Password:="mypassword"
Next WkSht

I have a lot of workbooks with this procedure fuctioning properly, however
there is one which each time results in Runtime error 57121 :
Applikcation-defined or object-defined error. Debugging points me to the line
: WkSht.Unprotect Password:="mypassword"

(In the same workbook I have a macro, using the same code, wich is working
without errors.)

Any idea how to solve this?

 

[Henk]

Thanks for you suggestion. I tried it, but it did not work.

I found out where the problem comes from. I have a UserForm with Listbox
that is used within several sheets. I used the "ControlSource" of the Listbox
as a place to store the choice of the user, which worked perfectly by the
way, but probably this is not the way to use the ControlSource. A soon as I
remove the UserForm, my OpenWorkbook() is functioning properly.

I am working on a way to make it work with UserForm.ListBox.ListIndex

tHenks again.

Try qualifying the worksheets:

For Each WkSht in Worksheets
becomes:
For Each WkSht in Me.Worksheets