Troubleshoot private sub workbook_open()

[untern8]

I have some code that automatically generates a filename with today's date from a template file. The code works great IFF excel is completely closed. If any workbooks are open, the code simply does not run.

Here's a sample of my code:

Option ExplicitPrivate Sub Workbook_Open()
    AddDate
    filename_cellvalue
    OpenPtNames
            
End Sub




Sub AddDate()
    Range("A1").Select
    ActiveCell.Value = Date
    Range("A3").Select
End Sub



Sub filename_cellvalue()
Dim Path As String
Dim filename As String
Path = "C:\Users\nunterseher.JCC\Desktop\"
filename = Range("d1").Value & ".xlsx"
ActiveWorkbook.SaveAs Path & filename, xlOpenXMLWorkbook
End Sub




Sub OpenPtNames()
    Workbooks.Open filename:="C:\Users\nunterseher.JCC\Documents\ZZ Daily other clinics\Clifty Patient Names.xlsx"
    ActiveWindow.WindowState = xlMinimized
End Sub

Why does this happen? What can I do to force this macro to run regardless of what workbooks are open?

Thanks,
Nate Unterseher

 

[mole999]

you have active.workbook, so when you switch focus that has an effect, use this.workbook, or even better have the name when possible.

is the code on a sheet or the workbook module

 

[MARK858]

As mole999 has asked where have you put the various sub's exactly? and what message boxes appear if you change the Workbook open code to

Private Sub Workbook_Open()
MsgBox "1"  
   AddDate
MsgBox "2"
    filename_cellvalue
MsgBox "3"
    OpenPtNames
MsgBox "4"         
End Sub

 

[untern8]

Gentlemen,

Thanks for the prompt response.

This code is in fact in the 'This Workbook' module.

Let me test the MsgBox question and get back to you

 

[untern8]

Ok. Forget everything we've worked on. My problem persists, but the example I chose had too many other variables in it. Let's try a simpler one. For 'this file', when it opens, I have written code to open another workbook.

**Try to look past why I need to do this and focus on the issue here.**

The issue is that if Excel is closed when 'this file' is opened, the code runs beautifully. If Excel has another workbook open and I try to open 'this file', the code does not run. 'this file' opens, but the other workbook does not.

Here's the code:

Option Explicit


Private Sub Workbook_Open()
    OpenPPL
   


End Sub


Sub OpenPPL()
    Workbooks.Open Filename:="Z:\aaCT Reports\Provider Patient List.XLS"
    ActiveWorkbook.Close True
    
End Sub

I understand that ActiveWorkbook.Close True will only work on the ActiveWorkbook (rather than ThisWorkbook), but the code doesn't even do the line before it...

 

[MARK858]

**Try to look past why I need to do this and focus on the issue here.**

The questions asked earlier were to focus on the issue including the MsgBoxes to find out exactly where it was breaking down.

So answer the questions asked and on top report back what error messages/messages Excel gives you.

We will try again with your last code, as you state

but the code doesn't even do the line before it

What message boxes show and what messages does Excel give you?

Private Sub Workbook_Open()
    OpenPPL
   


End Sub

Sub OpenPPL()
MsgBox "1"
    Workbooks.Open Filename:="Z:\aaCT Reports\Provider Patient List.XLS"
    ActiveWorkbook.Close True
MsgBox "2"    
End Sub

Btw, as far I am concerned Sub OpenPPL belongs in a regular module.

 

[untern8]

Mark858. Thank you for your help. I don't want to appear ungracious. I wasn't trying to avoid your suggestion, but I realized that the code in my original example wasn't even doing what I wanted it to do in the first place so it wasn't a good example to use for troubleshooting.

Here's a link to another thread describing the same problem:

https://stackoverflow.com/questions/5438548/excel-workbook-open-event-macro-doesnt-always-run

**Begin quote**
I've got a Workbook_Open event macro (and it's in ThisWorkbook) that doesn't always run.

• If Excel is closed and I double-click the .xls file from Windows Explorer, it does run.
• If I launch Excel from Start/Programs (with no .xls file) and then open the file, it does run.
• If I've already opened the file in Excel, but then close the file (leaving Excel open) and reopen it, then the macro does not run.

I've got security set to medium and I enable macros whenever it opens.
What do I need to do to get the macro to run whenever I open it, not just the first time for this Excel session?
**End Quote**


I have tried every recommendation, but am not solving the problem...

In answer to your questions, if another workbook is open and I run the code as recommended, the private sub workbook_open() doesn't even fire. If I open the file with excel closed, then I get a MsgBox with "1" in it. When I click "ok", it opens the file in question. While I like your approach to 'Debugging' it doesn't help us here because the problem seems to be with excel/vba not even starting the script....

 

[untern8]

Btw, as far I am concerned Sub OpenPPL belongs in a regular module.

I have no formal training in VBA and am trying to learn as much as possible. Please help me understand the logic here. I assume that you would leave the private sub workbook_open() in ThisWorkbook module (or it won't fire), correct?

 

[MARK858]

While I like your approach to 'Debugging' it doesn't help us here because the problem seems to be with excel/vba not even starting the script....

It does help because I needed to be 100% certain that it wasn't triggering the sub.

I tried putting the code in a workbook (the open code in ThisWorkBook, the OpenPPL in a regular module), saved it to my desktop, closed the file, left excel open and opened the workbook again via excel.

The code ran and gave both message boxes (and yes it did open and close the file) so am I doing something different to you?

I have no formal training in VBA and am trying to learn as much as possible. Please help me understand the logic here

ThisWorkBook and Sheet modules are there to perform a specific function i.e. code that only pertains to those objects.
In general this means workbook event code or worksheet event code.
Anything else should be (IMHO) in a regular module mainly because it is surprising how many times people don't realise what they are referencing when a regular sub is in one of these specific modules.

Basically by default put regular subs in regular modules unless there is a very specific reason not to (btw I won't go into Class modules now).

I assume that you would leave the private sub workbook_open() in ThisWorkbook module (or it won't fire), correct?

Yes

Just from interest as you have the subs currently what happens with

ThisWorkbook.OpenPPL

rather than

OpenPPL

 

[MARK858]

Obviously read the previous post first.

But just for an example of

I have no formal training in VBA and am trying to learn as much as possible. Please help me understand the logic here

Open a blank workbook, put the code below in a regular module, leave the VBE open and open a new blank workbook so it is the Active Workbook, click the VBE and run the code.

Sub METOO()
Sheets("Sheet1").Cells(1, 1) = "xxx"
End Sub

Now do exactly the same but this time with the code in the ThisWorkbook module.

See where the xxx ended up in each case?