Simple Macro help

K

Kevlar

I need a single macro that takes the user to a different cell dependin
on which sheet he is in. For instance if he is in the sheet "Stock" an
he activates the macro I want the cell F3 to be selected but if he i
in the sheet "Custom" I want the cell D14 to be selected.

Is this possible
 
B

Bob Phillips

Here is some code.

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Select Case h.Name
Case "Stock": Range("F3").Select
Case "Custom": Range("D14").Select
End Select
End Sub

This code goes into the ThisWorkbook code module. ADd more cases for other
sheets.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
S

SidBord

Are you familiar with Visual Basic? First, write a new
function that you can use other places too:

Function SheetName() as String
SheetName = Range("A1").Parent.Name
End Function

Then in your main macro you might use something like this:

Dim ShtNm as String, TgtCellAddr as String
ShtNm = SheetName 'Fetch the name of the active sheet
Select Case ShtNm
Case "Stock": TgtCellAddr = "$F$3"
Case "Custom": TgtCellAddr = "D14"
End Select
Range(TgtCellAddr).Select

Hope it works for you. I use this all the time.
 
Top