AutoRun Macro

  • Thread starter Thread starter jee22
  • Start date Start date
J

jee22

Can some one tell me how to run a macro as soon as the sheet is opened. I
have a box which needs to be filled using Add.item when the sheet opens.

The Additem routine works correctly when I click the button for it, but I
want to dispense with the button.

Thank you in advance.
 
Hi
you can use a workbook event for this.
If you mean the file openening use the workbook_open event
 
Not quiet Frank. I have a list box on the sheet which has a button next to
it
the button activates a macro to fill the list box with data.

What I want to do is for the macro to run when I open the sheet, thus
negating the need for the button

regards
J
 
Did you select another sheet in the same workbook, then come back to the sheet
with the listbox?

If yes, did you put the worksheet_activate code behind the worksheet that held
the listbox.
 
The box did not fill with the data when I opened the sheet. I am using
office 2000 on win2000
Thanks
J
 
Dave
There is only one sheet in this work book. The code is applied to that sheet
in the VBA panel
Thanks
 
Dave and Frank

I opened a new work book and entered just this code in Private Sub
Worksheet_Activate()
Dim n, p, q
Sheet1.lb1.Clear
Sheet1.lb2.Clear
Sheet1.lb3.Clear
For n = 1 To 12 Step 0.25
Sheet1.lb1.AddItem (n * 25.4)
Sheet1.lb2.AddItem (n * 25.4)
Next
For n = 6 To 25 Step 0.5
Sheet1.lb3.AddItem (n)
next

I then saved it and the sheet and closed the sheet.

Opening the sheet I briefly saw the data fill the boxes fill with data and
then blank. So it is working but the data is not visible. A slightly
different slant on the problem.
Can either of you wizards help?

Thanks
 
Try putting the code in the workbook_open event.

The worksheet_activate code will fire when you switch from a different
worksheet (in the same workbook) to this sheet.

But since you only have one worksheet, you don't want to use this
event.

You may be interested in downloading Chip Pearson's events sample
workbook.

It'll show you just when these events fire.

http://www.cpearson.com/excel/events.htm


jee22 said:
Dave
There is only one sheet in this work book. The code is applied to that sheet
in the VBA panel
Thanks
Click to expand...
<<snipped>>
 
Try putting the code in the workbook_open event.

The worksheet_activate code will fire when you switch from a different
worksheet (in the same workbook) to this sheet.

But since you only have one worksheet, you don't want to use this
event.

You may be interested in downloading Chip Pearson's events sample
workbook.

It'll show you just when these events fire.

http://www.cpearson.com/excel/events.htm


jee22 said:
Dave
There is only one sheet in this work book. The code is applied to that sheet
in the VBA panel
Thanks
Click to expand...
<<snipped>>
 
Thanks again Dave and others for your help
J
Dave Peterson said:
Try putting the code in the workbook_open event.

The worksheet_activate code will fire when you switch from a different
worksheet (in the same workbook) to this sheet.

But since you only have one worksheet, you don't want to use this
event.

You may be interested in downloading Chip Pearson's events sample
workbook.

It'll show you just when these events fire.

http://www.cpearson.com/excel/events.htm


"jee22" <[email protected]> wrote in message
<<snipped>>
Click to expand...
 
Back
Top