Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Macro error when trying to open workbook
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 -- Thanks, Angie |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Macro error when trying to open workbook
Works fine for me.
What is the error message type you get? Gord Dibben MS Excel MVP On Thu, 5 Nov 2009 15:08:06 -0800, paankadu wrote: 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Macro error when trying to open workbook
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 -- Regards, OssieMac "paankadu" wrote: 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 -- Thanks, Angie |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Macro error when trying to open workbook
Hi again
You havent mentioned what the error is.. "paankadu" wrote: 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 -- Thanks, Angie |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Macro error when trying to open workbook
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. -- Thanks, Angie "Jacob Skaria" wrote: Hi again You havent mentioned what the error is.. "paankadu" wrote: 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 -- Thanks, Angie |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Macro error when trying to open workbook
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! -- Thanks, Angie "paankadu" wrote: 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. -- Thanks, Angie "Jacob Skaria" wrote: Hi again You havent mentioned what the error is.. "paankadu" wrote: 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 -- Thanks, Angie |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Macro error when trying to open workbook
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 --------------- Jacob Skaria "paankadu" wrote: 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! -- Thanks, Angie "paankadu" wrote: 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. -- Thanks, Angie "Jacob Skaria" wrote: Hi again You havent mentioned what the error is.. "paankadu" wrote: 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 -- Thanks, Angie |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Open Workbook error | Excel Discussion (Misc queries) | |||
Macro in 2nd Open workbook | Excel Discussion (Misc queries) | |||
Open Workbook Macro??? | Excel Discussion (Misc queries) | |||
Run a macro when I open a workbook | Excel Discussion (Misc queries) | |||
How to Run Macro in open workbook/s | Excel Discussion (Misc queries) |