Macro to run when a particular sheet is opened

[Derek N]

I'm trying to work out how to get a macro to run every time I open a
particular sheet of a workbook without having to create a button or 'run' the
macro.

 

[Gary''s Student]

Here is an example. Install the following event macro in the worksheet code
area:

Private Sub Worksheet_Activate()
MsgBox ("Hello Derek")
End Sub

It will run whenever the tab is activated. If your macro is in a standard
module, you can call it from the event macro.


Because it is worksheet code, it is very easy to install and use:

1. right-click the tab name near the bottom of the window
2. select View Code - this brings up a VBE window
3. paste the stuff in and close the VBE window

If you save the workbook, the macro will be saved with it.

To remove the macro:

1. bring up the VBE windows as above
2. clear the code out
3. close the VBE window

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

To learn more about Event Macros (worksheet code), see:

http://www.mvps.org/dmcritchie/excel/event.htm

 

[Jim Thomlinson]

Right click the tab you want and select view code. The VBE will open up. Just
above the code window are 2 drop downs. Change the one on the left from
General to Worksheet. When you do this a code stub for selection change will
be written. Now from the drop down on the right select the Activate event. A
code stub for activate will be added. You can now delete the code stub for
Selection Change.

Any code added to the activate code stub will run when the sheet is selected.

 

[Gord Dibben]

Right-click on the sheet tab and "View Code"

Copy/paste this code to the sheet module.

Private Sub Worksheet_Activate()
macroname
End Sub


Gord Dibben MS Excel MVP

On Fri, 24 Jul 2009 07:27:01 -0700, Derek N <Derek

 

[Derek N]

Thanks everyone. After a couple of endless l(oops), it's working perfectly.