Macro error when trying to open workbook

[paankadu]

I have been using this macro in several workbooks... each has a spearate
password and it has been working great... until I get to my latest workbook

I protect the sheets and have this code in the "This Workbook" so when it
opens it will update the date in each worksheet.. I have gone in and verified
my password for each sheet, have taken the protection off and put it back on
being very careful to make sure I am not misspelling anything, have changed
passwords and keep getting the same error. This is the same code I have used
in about 7 other workbooks (just changing the password for each one). I have
even deleted the code out and typed it in from scratch.

Anyone have any ideas why I am getting this error on only 1 workbook?


Private Sub Workbook_Open()
Dim Sh As Worksheet
For Each Sh In Sheets
With Sh
..Unprotect Password:="bear"
..Range("g2") = Date
..Protect Password:="bear"
End With
Next
End Sub

 

[Gord Dibben]

Works fine for me.

What is the error message type you get?


Gord Dibben MS Excel MVP

 

[OssieMac]

What is the error number/message that you are getting and what line of code
does it stop on?

Try inserting a msgbox and determine if a specific worksheet is giving the
problem and if so try copying the worksheet (right click the tab name and
select Move or copy and check the box to make a copy) and then deleting the
old worksheet and then rename the copied worksheet to the original worksheet
name.

Insert the msgbox as follows:-

Private Sub Workbook_Open()
Dim Sh As Worksheet
For Each Sh In Sheets
With Sh
MsgBox Sh.Name
..Unprotect Password:="bear"
..Range("g2") = Date
..Protect Password:="bear"
End With
Next
End Sub

 

[Jacob Skaria]

Hi again

You havent mentioned what the error is..

 

[paankadu]

Run-time error '1004':
The password you supplied is not correct. Verify that the CAPS LOCK key is
off and be sure to use the correct capitalization.

..Unprotect Password:="waste"

this is the line it errors out on first.

I have gone in and unprotected the workbook and reprotected it and made sure
everything is identical...like I said it has worked on the 1st 4 or 5
workbooks without a problem. Since many of the forms are very similar when i
have finished 1 workbook I do a file save as to the new name and go in and
change the protection passwords and the passwords in the code. It has done
this error on the last 2 workbooks now.


Also, when I have saved my original form from Furnace.xls to 11.5
Furnace.xls and go in and open up the 11.5 Furnace the date changes to the
current date. On the saved versions I need the date to remain what it was
originally entered as, not change to the current. How do I get it to remain
static on the saved copies. The Furnace.xls file will be the master form
that the user will go in each day and update and as they complete they will
save it as date_Furnace.xls

Thanks again!
Thanks again.

 

[paankadu]

OK, I found my problem. There was a 3rd worksheet that it was trying to
unprotect, but it wasn't protected. I have corrected that. Sorry to have
bothered you with a silly error, but am glad I found it.

On the other hand, the code listed in the original post works great to put
in the date, but when the user does a file save as date_Furnace.xls and they
go to reopen it a few days later it doesn't keep the original date entered.
It changes it to the date the file is being opened. How do I keep the
original date entered? The main file Furnace.xls is being used as a master
file and then after they enter their log information it is being saved as
date_Furnace and that is where the date needs to remain static without
anything being done by the user.

Thanks again!

 

[Jacob Skaria]

Good to hear you identified the problem. Try the below code which will put
the date only if cell G2 is blank...Hope that helps

Private Sub Workbook_Open()
Dim Sh As Worksheet
For Each Sh In Sheets
With Sh
..Unprotect Password:="bear"
If .Range("g2") = "" Then .Range("g2") = Date
..Protect Password:="bear"
End With
Next
End Sub

If this post helps click Yes