CODEWITHSUNDEEP
vbaexcel
8OutOf10Dogs
8OutOf10Dogs

Reputation: 11

Using Application.Run to open a workbook with protected worksheets and protected VBAProject

An employee at one of my company's local offices is working on a macro in Sheet1 of a workbook that would run a macro in another workbook using Application.Run:

Private Sub CommandButton1_Click()
Wfile = Range("B2").Value
Wpath = Range("B3").Value

Workbooks.Open Wpath + "/" + Wfile
Application.Run Wfile & "!copy_rates_macro"

End Sub

The workbook that he is trying to open/use is protected in every way possible (all of its sheets are protected and its VBAProject is protected as well.

When the macro is run, the 1004 run-time error message window pops up saying "Cannot rund the macro 'Name.xlsm!copy_rates_macro'. The macro may not be available in this workbook or all macros may be disabled.'

I did a lot of research and I thought the putting the following in the protected file would work:

Private Sub Workbook_Open()
'    Dim wSheet As Worksheet
'    For Each wSheet In Worksheets
'        wSheet.Protect Password:="pw", UserInterFaceOnly:=True
'    Next wSheet
    Application.EnableEvents = False
End Sub

Note that the parts commented out above are my additions to code that was already there and must remain there. Also, "pw" is the password for every worksheet and the VBAProject.

This code didn't make a difference (it wasn't commented out when I ran it), and I imagine it has something to do with the VBAProject being protected.

Is this request even possible, or is it a lost cause? My boss doesn't want the password to the protected workbook to be released but I can't see a way around it.

Thanks for any help.

Upvotes: 0

Views: 1180

Answers (1)

Rory
Rory

Reputation: 34045

You shouldn't have to unprotect a project to run code in it (that would make it pretty worthless), but I can see a couple of possible issues in your code. First you use "/" at the end of the path rather than "\" and second, if the workbook name contains spaces you need to enclose it in single quotes:

Workbooks.Open Wpath + "\" + Wfile
Application.Run "'" & Wfile & "'!copy_rates_macro"

If that still won't run, there are a few possible pitfalls: 1. The macro is in a module of the same name, or in an object module, such as a worksheet or ThisWorkbook, in which case you need to prefix the macro name with the code name of the object. 2. Automation Security may be disabling macros in the opened workbook. To work around that, try adding:

Application.AutomationSecurity = msoAutomationSecurityLow

before opening the workbook. Ideally, you should store the current value and reset that at the end.

Upvotes: 0

Related Questions