Runtime error 57121
 

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?

Runtime error 57121
 

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

regrds
Paul

On Feb 25, 11:17*am, Henk wrote:
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?

Runtime error 57121
 

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


" wrote:

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

regrds
Paul

On Feb 25, 11:17 am, Henk wrote:
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?

Runtime error 57121
 

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

On Feb 25, 12:10*pm, Henk wrote:
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



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

regrds
Paul

On Feb 25, 11:17 am, Henk wrote:
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?- Hide quoted text -

- Show quoted text -

Runtime error 57121
 

You have a response at your other post.

Henk wrote:

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?

--

Dave Peterson

Runtime error 57121
 

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.


"Dave Peterson" wrote:

Try qualifying the worksheets:

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







"Dave Peterson" wrote:

You have a response at your other post.

Henk wrote:

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?

--

Dave Peterson