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

[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

 

[Guest]

Try this
Run "UpdateData"

 

[Guest]

Your Events may be disabled. Try running this code to reset events. Then
retry your code...

Sub ResetEvents
application.enableevents = true
end sub

Note that enable events is one of those persistent settings that you need to
be very careful with. If you turn it off it will never get turned back on
again unless you do so...

 

[Ken]

I think either Call UpdateData or Run "UpdateData" should work. Make
sure the Workbook_Open code is attached to the ThisWorkbook object,
and not in a regular module.

Good luck.

Ken
Norfolk, Va

 

[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.

 

[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).

 

[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