Calling Workbook_Open() marco using a button.

[bposaner]

I got a Private Sub Workbook_Open() marco which runs fine and refreshes various listboxes.
However once opened I need to refresh various sheets, linked to access databases. This works fine, but then the list boxes could be wrong. So i need to rerun the Workbook_Open macro.
What i'm trying to do is add a button which will run the "Private Sub Workbook_Open()" macro.
The only way I can run the macro is open VBA, and manually run the macro.

I tried createing a macro and calling "Workbook_Open()" but it wasn't any good.

So your help would be great.

 

[Lewiy]

The Workbook_Open event cannot be called from anywhere else other than by opening the Workbook as it is a Private Event.

What I suggest you do is put your code in a Module and call it something like:
Sub OpenMacro()

Then under the Workbook_Open event, call this macro, i.e:

Private Sub Workbook_Open()
OpenMacro
End Sub

You can then also call that macro from a command button, i.e:

Private Sub CommandButton_Click()
OpenMacro
End Sub

 

[bposaner]

I had thought of that.
Just means more work to change the several workbooks I use this for
Was hoping for a simple answer.
Thanks.

 

[Norie]

Why not just remove the Private?

Then you can use something like this.

ThisWorkbook.Workbook_Open

 

[bposaner]

So will the macro still automatically run when the workbook opens?
And I will also be able to call it from a button?

 

[Norie]

Yes it will, well it did when I tested it.

The only disadvantage is that the workbook Open event will now be listed when you goto Tools>Macros...

 

[bposaner]

Thanks, works perfect. Just what I want.