I have a macro I would like to run when a partiuclar sheet is selected
in a workbook but cant find where that is covered. I would also like
that same macro to run when the sheet is closed and another sheet is
opened. Is there such a command in Excel? TIA


--
moorso
------------------------------------------------------------------------
moorso's Profile: http://www.excelforum.com/member.php...fo&userid=8966
View this thread: http://www.excelforum.com/showthread...hreadid=380501

Private Sub Worksheet_Activate()
'do your stuff
End Sub


'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.



--

HTH

RP
(remove nothere from the email address if mailing direct)


"moorso" wrote in
message ...

I have a macro I would like to run when a partiuclar sheet is selected
in a workbook but cant find where that is covered. I would also like
that same macro to run when the sheet is closed and another sheet is
opened. Is there such a command in Excel? TIA


--
moorso
------------------------------------------------------------------------
moorso's Profile:
http://www.excelforum.com/member.php...fo&userid=8966
View this thread: http://www.excelforum.com/showthread...hreadid=380501

Thank you Bob. It does run the macro but I note one small problem.
.... I added a worksheet_deactivate event so that the same macro woul
run when another worksheet is selected. Only problem is, the macr
wont allow any other sheet to be selected. Any thoughts on how to ru
the macro and select another sheet at the same time? Thanks very muc
for the help

--
moors
-----------------------------------------------------------------------
moorso's Profile: http://www.excelforum.com/member.php...nfo&userid=896
View this thread: http://www.excelforum.com/showthread.php?threadid=38050

If it is wanted for any sheet, use the workbook sheet select event


Private Sub Workbook_SheetActivate(ByVal Sh As Object)
'do your stuff
End Sub

'This is workbook event code.
'To input this code, right click on the Excel icon on the worksheet
'(or next to the File menu if you maximise your workbooks),
'select View Code from the menu, and paste the code


--

HTH

RP
(remove nothere from the email address if mailing direct)


"moorso" wrote in
message ...

Thank you Bob. It does run the macro but I note one small problem..
... I added a worksheet_deactivate event so that the same macro would
run when another worksheet is selected. Only problem is, the macro
wont allow any other sheet to be selected. Any thoughts on how to run
the macro and select another sheet at the same time? Thanks very much
for the help.


--
moorso
------------------------------------------------------------------------
moorso's Profile:
http://www.excelforum.com/member.php...fo&userid=8966
View this thread: http://www.excelforum.com/showthread...hreadid=380501

Thanks again Bob. That also works, but again I have the same problem.
The macro that is called to perform when any of the sheets is selecte
first calls up the sheet on which the macro runs, then runs the macr
then that sheet remains as the active sheet. For example, the macr
that sorts the data for the vlookup function is on sheet "labor".
There are 3 other sheets, I'll call Sheet1, Sheet2 and Sheet3. No
when I click on say Sheet1, it goes to the "labor" sheet, runs the sor
macro, then stays right there. How can I make it so that it runs th
macro on "labor" sheet then returns to sheet1, the one I originall
selected? I do appreciate your help with this

--
moors
-----------------------------------------------------------------------
moorso's Profile: http://www.excelforum.com/member.php...nfo&userid=896
View this thread: http://www.excelforum.com/showthread.php?threadid=38050

Moorso,

Normally if you want to work on another worksheet, you don't activate it,
you just reference that worksheet from your code. So for instance, if you
wanted to run a macro against Sheet2, you would use something like

With Worksheets("Sheet2")
.Range("A1").Value = 17
'etc
End With

using this method, Sheet2 does not have to be selected. Can we use an
approach like this, or are you using Activate to trigger the macro.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"moorso" wrote in
message ...

Thanks again Bob. That also works, but again I have the same problem.
The macro that is called to perform when any of the sheets is selected
first calls up the sheet on which the macro runs, then runs the macro
then that sheet remains as the active sheet. For example, the macro
that sorts the data for the vlookup function is on sheet "labor".
There are 3 other sheets, I'll call Sheet1, Sheet2 and Sheet3. Now
when I click on say Sheet1, it goes to the "labor" sheet, runs the sort
macro, then stays right there. How can I make it so that it runs the
macro on "labor" sheet then returns to sheet1, the one I originally
selected? I do appreciate your help with this.


--
moorso
------------------------------------------------------------------------
moorso's Profile:
http://www.excelforum.com/member.php...fo&userid=8966
View this thread: http://www.excelforum.com/showthread...hreadid=380501