How do I auto run my macro upon open of file....Workbook_Open() is NOT working?

M

meghantrus

I need to run a Macro named UpdateData upon open of the file.
This code is not working. I placed the below code in ThisWorkbook.
I am able to manually run UpdateData, but it does not run
automatically when I open the file.
Security is set to Medium and I enable Macro's when I open the file.
What am I doing wrong?


Private Sub Workbook_Open()
Call UpdateData
End Sub
 
D

Dave Thomas

Suggest you go into visual basic, select This Workbook, double click and
step through the code one line at a time using function key F8. You'll see
what the code does.
 
H

Harlan Grove

(e-mail address removed) wrote...
I need to run a Macro named UpdateData upon open of the file.
This code is not working. I placed the below code in ThisWorkbook.
I am able to manually run UpdateData, but it does not run
automatically when I open the file.
Security is set to Medium and I enable Macro's when I open the file.
What am I doing wrong?

Private Sub Workbook_Open()
Call UpdateData
End Sub

If for some reason Application.EnableEvents had been set to FALSE, no
event handlers would run, not even Open events. However, with the file
open, you could define the name Auto_Open referring to

='YourFilenameHere.xls'!ThisWorkbook.Workbook_Open

and it'll run on opening if you enable macros even if EnableEvents
were set to FALSE. However, it'd run twice if EnableEvents were set to
TRUE. You'd need to add a state variable to prevent that.

Private Sub Workbook_Open()
Static st As Boolean
If Not st Then
Call UpdateData
st = True
End If
End Sub

Even so, if UpdateData throws a runtime error, and you end it, the
static variable st will reset to FALSE, so this would try to run
UpdateData a second time. You could use a defined name or a worksheet
cell as the state variable, but that'd mark the file as modified
(which may not be a big deal).
 
F

FrankSinatra

I dont know if this will help, but I always forget that the "Private
Sub Workbook_Open()" statements have to go in the "Microsoft Excel
Objects" section of the VBA Project, in "ThisWorkbook".

The mistake I make almost everytime is to first look for the Open
command in the drop down boxes above the VBA coding window, and it is
not there of course. Then I check in the Excel Help and find the
"Private Sub Workbook_Open()" command example and do some macro
recording to get close to what I what the spreadsheet to do on Open.
Then I drop that into Module1 of the modules section, and of course
that doesnt work---but I am closer! More frustrated, but closer. So
the next thing I do is figure I need to rename Module1 to AutoExec,
but that doesnt work either.

So I then fish around for an old Excel file I have where the "on Open"
code works, and viola. I move the code I just wrote out of Module1/
Autoexec and into "ThisWorkbook" and it runs fine.

Take care.
Keving Gaza

"What you focus on, you enlarge."
Stedman Graham
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top