Workbook_Open Not working!!!

[atf32]

Working in MS Excel 2010

HELP!! I have a problem where "Workbook_Open" does not execute when I open the file. It is in a trusted location and I even put "stop" in the beginning of the code, before I saved and closed the file.... And the code never executed. I even created a simple blank macro-enabled file, with a "Workbook_Open" sub, that included a "stop" line, and that works as expected (i.e., stopped on "stop" line).

 

[Jerry Sullivan]

A few things to try....

Check that the code is pasted in the ThisWorkBook module of the Workbook. It won't work if it is in a Sheet Code Module or Standard Code Module.

Paste this code into a Standard Code Module in your problem workbook and run the macro.
This will trouble shoot two potential causes for what you describe.
- Macros not enabled (if any message box appears you know that isn't the problem).
- EnableEvents set to False. (Workbook_Open won't execute if EnableEvents=False).

Sub EnableEventsNow()
    Dim sMsg As String
    With Application
        sMsg = IIf(.EnableEvents, _
            "Events were already enabled.", _
            "Events are now enabled.")
        .EnableEvents = True
    End With
    MsgBox sMsg
End Sub

 

[atf32]

Thanks for the response. The code is in the Thisworkbook module and when I run that code (above), I get the message box that tells me that "Events are already enabled". But the Workbook_Open will still only execute if I manually execute it.

 

[shg]

Is the function signature correct, i.e., exactly this:

Private Sub Workbook_Open()

 

[atf32]

Thanks, but I have that.

 

[Jerry Sullivan]

Sometimes a "divide and conquer" approach to a problem like this can be used to isolate the cause.

You found that your Workbook_Open code worked as expected when placed in a new blank macro-enabled workbook.

Perhaps try moving parts of your problem workbook into that new workbook (one sheet at a time, one code module at a time...) testing at each step whether the problem workbook continues to fail and whether the new workbook continues to work correctly.

At some point, if the results flip, you can dig deeper into the part that just moved.
If you are able to successfully move all the contents into your new workbook and still have it open correctly, you may not have solved the mystery, but you'll have a "fixed" version of your workbook.

 

[atf32]

Thanks for the help....... I renamed the "Workbook_open" procedure and then recreated a new version. Then I rebuilt the code a little at a time and found an error. However, I still don't know why the code would not stop at the "stop" line. Anyways , thanks for the help.

 

[Dan_Excel2010]

Hi Jerry, I tried adding and running the macro code that you provided below to my Excel 2010 workbook but instead of either message I get the same "File not found: VBA6.DLL" error that I receive for Private Sub Workbook_open().

A few things to try....

Check that the code is pasted in the ThisWorkBook module of the Workbook. It won't work if it is in a Sheet Code Module or Standard Code Module.

Paste this code into a Standard Code Module in your problem workbook and run the macro.
This will trouble shoot two potential causes for what you describe.
- Macros not enabled (if any message box appears you know that isn't the problem).
- EnableEvents set to False. (Workbook_Open won't execute if EnableEvents=False).

Sub EnableEventsNow()
    Dim sMsg As String
    With Application
        sMsg = IIf(.EnableEvents, _
            "Events were already enabled.", _
            "Events are now enabled.")
        .EnableEvents = True
    End With
    MsgBox sMsg
End Sub

 

[Jerry Sullivan]

Hi Dan, That could be a problem with the way your Excel is installed. There isn't anything special about my code example that should cause that- you'll probably get that error running any VBA code.

Doing a quick web search, this thread might help you to fix that...
vba - Excel - "File Not Found: VBA6.DLL" - Stack Overflow


If the file VBA6.dll has been deleted (or was never installed) in your Windows system folder, you will need to repair that.

 

[PinoyAko]

"Check that the code is pasted in the ThisWorkBook module of the Workbook..."

Thanks a lot Jerry! This piece of information solved my concern.