How to make a macro run first before opening an Excel Spreadsheet?? 2

[dannydanny]

Hi,

I`ve got a list box on my Excel Spreadsheet. I want to populate the list box before any user interaction, that is, I want a macro to run that will fill the list box upon opening the file. Is it possible to have a macro run upon opening the file it resides on??

Any info much appreciated,
Danny.

 

[Nikita6003]

Hi Danny

An Excel workbook has a Workbook_Open event, which fires when it's opened
To add this even:
1. go to the VBA Editor ([Alt-F11]
2. double click on the ThisWorkbook object in the Project Explorer (left-hand side of the screen)
3. You'll see the ThisWorkBook code module appear - this may or may not have the line

Option Explicit

entered (to force you to explicitly declare all & any variables you want to use)
4. Select WORKBOOK from the left-hand dropdown at the top of the code module
5. If necessary, select OPEN from the right-hand dropdown, but after step 4. you *should* see the following code appear:

Private Sub Workbook_Open()

End Sub

Add the code to populate your listbox & save - when you open the workbook it'll always populate the listbox first

Oh, and to open a workbook without firing the Workbook_Open event: hold down the [Shift] key when opening the workbook

HTH

Cheers
Nikki

 

[dannydanny]

Hi Nikki,

Thanks for that info, it was exactly what I was after. And the tip about holding down "Shift" will surely come in handy too!!

Thanks again,
Danny