Code not running when workbook opened

[h4a4r6r6]

Hi,


As per the title, the below code is not executing when I open the workbook. Sometimes it executes as intended and other times it doesn't. Can anyone suggest a reason why and offer a solution please. The workbook has password protection and is used by other people in my workplace so for them is opened read only.

[/COLOR]Option ExplicitPrivate Sub Workbook_Open()


    'Maximise the window when the workbook is opened
    Application.WindowState = xlMaximized


    'Show the navigation bar
    Call show_navigation


    'Activate worksheet
    AppActivate ThisWorkbook.Application



End Sub[COLOR=#333333]

Thank you.

 

[Joe4]

Welcome to the Board!

If they do not enable macros/VBA, the code will not run. And you cannot force them to enable macros/VBA (which is good, otherwise people could "force" viruses upon you!).

 

[h4a4r6r6]

Welcome to the Board!

If they do not enable macros/VBA, the code will not run. And you cannot force them to enable macros/VBA (which is good, otherwise people could "force" viruses upon you!).

Thank you for the reply Joe4. I understand about not forcing people to enable macros and the reasons for this however, the macro is not executing when I personally open the workbook even though I have macros enabled. Any thoughts on why this would be please?

Thank you.

 

[Joe4]

Can you post the code for you "show_navigation" procedure?

Note that this is "Event Procedure" code. So if you had other code you recently run that disabled events from running, this code would not be triggered when the file is opened.
When this doesn't run, what happens if you totally close out of all Excel sessions, re-open Excel, then open the workbook? If that works, then I suspect some other code that you previously run had disabled events.

One way to determine whether or not your event code is really running is to put a Message Box at the top of the code, i.e.

Private Sub Workbook_Open()

[COLOR=#ff0000]    MsgBox "Workbook_Open code is running"[/COLOR]

    'Maximise the window when the workbook is opened
    Application.WindowState = xlMaximized

    'Show the navigation bar
    Call show_navigation

    'Activate worksheet
    AppActivate ThisWorkbook.Application

End Sub

So if you save that, and try again, and the code doesn't seem to be doing what you want, here is what it tells you:
- If you do not get the message box, then the code is not being called, meaning that either Macros/VBA are being disabled, or events have been disabled.
- If you do get the message box, but it is still not doing what you want, that means there is an issue with your code.

 

[h4a4r6r6]

Can you post the code for you "show_navigation" procedure?

Note that this is "Event Procedure" code. So if you had other code you recently run that disabled events from running, this code would not be triggered when the file is opened.
When this doesn't run, what happens if you totally close out of all Excel sessions, re-open Excel, then open the workbook? If that works, then I suspect some other code that you previously run had disabled events.

One way to determine whether or not your event code is really running is to put a Message Box at the top of the code, i.e.

Private Sub Workbook_Open()

[COLOR=#ff0000]    MsgBox "Workbook_Open code is running"[/COLOR]

    'Maximise the window when the workbook is opened
    Application.WindowState = xlMaximized

    'Show the navigation bar
    Call show_navigation

    'Activate worksheet
    AppActivate ThisWorkbook.Application

End Sub

So if you save that, and try again, and the code doesn't seem to be doing what you want, here is what it tells you:
- If you do not get the message box, then the code is not being called, meaning that either Macros/VBA are being disabled, or events have been disabled.
- If you do get the message box, but it is still not doing what you want, that means there is an issue with your code.

Code for show_navigation:

Sub show_navigation()
    frm_navigation.Show vbModeless
End Sub

Tried adding the msgbox as above, closed Excel, re-opened the workbook but no message box. Where would I add the enable events if that's the cause of my issue please?

 

[Joe4]

You must have some code running somewhere that is disabling your events from running.
That code looks like this:
Application.EnableEvents = False
You typically see this code in Worksheet_Change or Worksheet_SelectionChange event procedures. What that does is keeps changes or selection changes made by the code itself from invoking itself, and getting caught in an endless loop. Typically, you will see code at the end of those procedures to turn it back on, like this:
Application.EnableEvents = True

If you have the first line disabling events and not the second, or if your code exits before you get to the second (to re-enable events), you can see that issue.

You can make sure that codes are enabled at any time by manually running this procedure:

Sub ReenableEvents()
    Application.EnableEvents = True
End Sub

So try running that first, then open your workbook. If you STILL do not get that message box, it most likely means 1 of 2 things.
Either:
1. VBA/Macros are NOT enabled
2. You placed the Workbook_Open code in the wrong module, so it is not being invoked (it MUST be in the ThisWorkbook module of the workbook you are opening in order to fire automatically).

 

[h4a4r6r6]

You must have some code running somewhere that is disabling your events from running.
That code looks like this:
Application.EnableEvents = False
You typically see this code in Worksheet_Change or Worksheet_SelectionChange event procedures. What that does is keeps changes or selection changes made by the code itself from invoking itself, and getting caught in an endless loop. Typically, you will see code at the end of those procedures to turn it back on, like this:
Application.EnableEvents = True

If you have the first line disabling events and not the second, or if your code exits before you get to the second (to re-enable events), you can see that issue.

You can make sure that codes are enabled at any time by manually running this procedure:

Sub ReenableEvents()
    Application.EnableEvents = True
End Sub

So try running that first, then open your workbook. If you STILL do not get that message box, it most likely means 1 of 2 things.
Either:
1. VBA/Macros are NOT enabled
2. You placed the Workbook_Open code in the wrong module, so it is not being invoked (it MUST be in the ThisWorkbook module of the workbook you are opening in order to fire automatically).

Thanks for the help with this but still can't get it to work.

I have some code in some worksheets like:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Cells.Count > 1 Or Target.HasFormula Then Exit Sub
        On Error Resume Next
        If Not Intersect(Target, Range("A25")) Is Nothing Then
            Application.EnableEvents = False
            Target = UCase(Target)
            Application.EnableEvents = True
        End If
    If Range("A25").Value <> "" Then
        Range("A25").Select
    End If
        On Error GoTo 0
End Sub

I have tried running the reenable_events procedure that you mentioned above, closed the file and re-opened but still the same. Macros are enable and the Workbook_Open procedure is placed in ThisWorkbook.

Private Sub Workbook_Open()
    Call reenable_events
    MsgBox "test"
    Application.WindowState = xlMaximized
    Call show_navigation
    AppActivate ThisWorkbook.Application
End Sub

So I'm a little stumped here .

 

[Joe4]

Putting this line inside your code does no good:

Call reenable_events

If events are disabled, the code will not run, so this procedure will not be run.
You cannot place the fix inside the code itself. As I said, you would need to manually run it as a standalone procedure like I showed.
Try closing your workbook, opening a new blank workbook within that same Excel session, run that re-enable event code manually, and then try re-opening your workbook with the Workbook_Open code, and see if your message box appears.

If that works, then something else is shutting off your events before you open this workbook, and you need to address that. It could be from another workbook you already have open in the same session.

 

[Fluff]

Cross posted https://www.excelforum.com/excel-pr...73-code-not-running-when-workbook-opened.html

While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules).
This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

 

[h4a4r6r6]

Putting this line inside your code does no good:

Call reenable_events

If events are disabled, the code will not run, so this procedure will not be run.
You cannot place the fix inside the code itself. As I said, you would need to manually run it as a standalone procedure like I showed.
Try closing your workbook, opening a new blank workbook within that same Excel session, run that re-enable event code manually, and then try re-opening your workbook with the Workbook_Open code, and see if your message box appears.

If that works, then something else is shutting off your events before you open this workbook, and you need to address that. It could be from another workbook you already have open in the same session.

Tried the above and still the same unfortunately.