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

Bob, I am currently using the selection of a sheet to trigger the macr
on the "labor" sheet. I dont know how to run the macro on this shee
without first having the macro select the "labor" sheet. I would lov
to have the macro run without having to select this sheet if I knew ho
to do it.

In other words, I have a macro called crew_sort. This macro sorts th
crews in ascending order so that I can use the vlookup function on th
other workbook sheets to grab the information from the labor sheet.
Apparently vlookup has to work with sorted data in the ascending orde
for it to work properly. With the "Workbook_SheetActivate" yo
suggested, I now have it run the "crew_sort" macro which physicall
selects the "labor" sheet, runs the macro and sits right there. Th
outcome is I can't actually select the other sheets in the workboo
because the macro makes it stick on the "labor" sheet. I know thi
sounds confusing and I'll try to understand how to run a macro withou
actually going to the sheet it works on. I dont know how yet..:

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

Bob,

here is the macro that I want to run when the crew sheet is deselected
and any other sheet is selected. I have been calling this sheet
"labor" by mistake. It actually is called "crews".



Sub Sort_Crews()
'
Sheets("Crews").Select
Sheets("Crews").Unprotect

Sheets("Crews").Columns("S:AA").Copy
Range("AB1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

Application.Goto Reference:="CREWSUM"
Selection.Sort Key1:=Range("AD3"), Order1:=xlAscending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom,
_
DataOption1:=xlSortNormal
Selection.EntireColumn.Hidden = True



Application.CutCopyMode = False
Sheets("Crews").Protect DrawingObjects:=True, Contents:=True,
Scenarios:=True

End Sub


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

Untested but use complete sheet references when working with your range
and you can avoid selecting the crews sheet.


Code:
--------------------
Sub Sort_Crews()
'
Sheets("Crews").Unprotect

With Sheets("Crews")
.Columns("S:AA").Copy
.Range("AB1").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False

Application.Goto Reference:="CREWSUM"
.Range("AB:AJ).Sort Key1:=.Range("AD3"), Order1:=xlAscending, _
Header:=xlGuess, OrderCustom:=1, MatchCase:=False, _
Lotation:=xlTopToBottom, DataOption1:=xlSortNormal _
Selection.EntireColumn.Hidden = True

Application.CutCopyMode = False
.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
End With

End Sub
--------------------


HTH


--
bhofsetz
------------------------------------------------------------------------
bhofsetz's Profile: http://www.excelforum.com/member.php...o&userid=18807
View this thread: http://www.excelforum.com/showthread...hreadid=380501

I get a compile error....something is wrong with the code here....looks
to be this part of it....


Range("AB:AJ).Sort Key1:=.Range("AD3"), Order1:=xlAscending,
Header:=xlGuess, OrderCustom:=1, MatchCase:=False, _
Lotation:=xlTopToBottom, DataOption1:=xlSortNormal _
Selection.EntireColumn.Hidden = True

my head is starting to spin..:)


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

try putting the complete sheet reference before the sort key. I assumed
since it was within the With statement that it would pick that up but it
may not be in this case.

Add the red text to the existing line

Range("AB:AJ).Sort Key1:=Sheets("Crews").Range("AD3"),
Order1:=xlAscending

Give that a try and post back with results.


--
bhofsetz
------------------------------------------------------------------------
bhofsetz's Profile: http://www.excelforum.com/member.php...o&userid=18807
View this thread: http://www.excelforum.com/showthread...hreadid=380501

No luck. Still getting a compile error. Could it have something to d
with the fact that the name "CREWSUM" is being called out in the middl
of the 'with' statement? I'm not real experienced at some of this a
you can probably tell

--
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" wrote in
message ...

Apparently vlookup has to work with sorted data in the ascending order
for it to work properly.

It doesn't need to be, you can use an extra parameter for unsorted data as
long as you don't want an approximate match.

=VLOOKUP(value, lookup_range, offset,False)

What text is highlighted when you receive the compile error

--
bhofset
-----------------------------------------------------------------------
bhofsetz's Profile: http://www.excelforum.com/member.php...fo&userid=1880
View this thread: http://www.excelforum.com/showthread.php?threadid=38050

I caught a couple of typos that I made, my appologies.

Try this:


Code:
--------------------
Sub Sort_Crews()
'
Sheets("Crews").Unprotect

With Sheets("Crews")
.Columns("S:AA").Copy
.Range("AB1").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False

Application.Goto Reference:="CREWSUM"
.Range("AB:AJ").Sort Key1:=.Range("AD3"), Order1:=xlAscending, _
Header:=xlGuess, OrderCustom:=1, MatchCase:=False, _
Lotation:=xlTopToBottom ', DataOption1:=xlSortNormal
.Range("AB:AJ").EntireColumn.Hidden = True

Application.CutCopyMode = False
.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
End With

End Sub
--------------------


--
bhofsetz
------------------------------------------------------------------------
bhofsetz's Profile: http://www.excelforum.com/member.php...o&userid=18807
View this thread: http://www.excelforum.com/showthread...hreadid=380501

Nope....I'm at home and dont have the original code here, but have bee
trying it on some slightly different stuff. I wonder if the proble
might be that the code asks to select the named range "CREWSUM".
Wouldnt that necessarily take it to the sheet that the named range i
on which is the Crews sheet

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

That is definitely a possibility. I'm not extreemly experienced wit
named ranges but I know you can use a named range from one worksheet o
another without having to switch sheets. What is the purpose of tha
line in your code? Have you tried commenting it out and run the cod
without that line

--
bhofset
-----------------------------------------------------------------------
bhofsetz's Profile: http://www.excelforum.com/member.php...fo&userid=1880
View this thread: http://www.excelforum.com/showthread.php?threadid=38050

Bob Phillips Wrote:
"moorso" wrote in
message ...

Apparently vlookup has to work with sorted data in the ascending
order
for it to work properly.

It doesn't need to be, you can use an extra parameter for unsorted data
as
long as you don't want an approximate match.

=VLOOKUP(value, lookup_range, offset,False)

Oh baby, that just might do the trick! If I dont have to run a macro
to sort the data I should in business. I'll try it tomorrow on the
real spreadsheet, but I cant see why my problem isnt solved (the easier
way!). Thanks a million Bob and bhofsetz.


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