Macros not running in Excel 2013

[vmottert]

I just loaded Office 2013 on my PC, but still have my trusty 2010. I have a lot of workbooks with VB macros in them that work fine in Excel 2010. But they won't run in 2013. I get this error message:

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

The workbooks are being opened from a "trusted location". I have even tried lowering the macro security to "Enable all macros...". The macros are password protected, but I have not been asked for the password. Hope someone knows where this is coming from, and how to get rid of it.

Thanks, Vernon

 

[vmottert]

Later. I have figured out what is wrong. The sheets in my workbooks are all password protected. I have macros that do things on the worksheets (sort, extract data, etcetera). These macros must unprotect and protect the worksheets to do these things. I have been using code in the macros to do this in the following form:

Sheets("SheetName").Unprotect ("password"), or
Sheets("SheetName").Protect ("password")

The above has worked fine in Excels 2000-2010, but it does not work in Excel 2013. The syntax that works in Excel 2013 is:

Sheets("SheetName").Unprotect Password:="password", or
Sheets("SheetName").Protect Password:="password"

The differences of course are the addition of “Password:=” in the new form and the removal of the parentheses () around the password.

Now I have around 25 worksheets each with several pages of VB macro code with the old password code in it that won’t work in Excel 2013. I would love to find a Search and Replace that would make those changes. That way I could fix this a page at a time in the VB Editor. Here is what I have so far:

Find what: protect
Replace with: Protect Pasword:=

This works to add the “Password:=” to the code. The trick is that there are many passwords, so I will need some combination of wildcards and quote marks to get it to leave the password in place, with only quote marks around it – no (). Can this be done? Here’s hoping someone can help me with the code that I can paste into the Search and Replace fields to make these changes a page at a time in the VB Editor. Thanks in advance for any help.
Vernon

 

[bljfur]

We are using the syntax below in Excel 2013 (what they say above that works). However, we are still receiving the same error message. Any ideas on why this is happening?

Sheets("SheetName").Unprotect Password:="password", or
Sheets("SheetName").Protect Password:="password"

 

[RoryA]

1. Are you certain the password is actually correct?
2. Can you post an example of the exact code used? If not, are you sure you are using:

Sheets("SheetName").Unprotect Password:="password"

and not

Sheets("SheetName").Unprotect Password = "password"

by mistake?

 

[bljfur]

Yes, we use this code in 2003 and 2010 and it works there. Here is the code.

Public Const IPT_toolPWWB As String = "SOSwbv3.0" 'workbook password
ThisWorkbook.Unprotect Password:=IPT_toolPWWB
Worksheets(ShtName).Visible = True
ThisWorkbook.Protect Password:=IPT_toolPWWB

1. Are you certain the password is actually correct?
2. Can you post an example of the exact code used? If not, are you sure you are using:

Sheets("SheetName").Unprotect Password:="password"

and not

Sheets("SheetName").Unprotect Password = "password"

by mistake?

 

[RoryA]

Can you manually unprotect the workbook using that password?

 

[bljfur]

Yes

 

[RoryA]

I'm afraid I cannot replicate that - the code works perfectly in 2013 for me.