Selecting Worksheets

[colinh]

Hi All
Need a bit of code I have a workbook with 100 odd worksheets on is there a way of selecting or activating a worksheet by typing in a number in a cell on sheet 1?

So to sum up if sheet1 cell A1 I enter 52 we automaticaly jump to worksheet 52. I can use a userform or something if needed I just need the code for jumping to the chosen worksheet.

Thanks a mil

Colin

 

[MrKowz]

Are the worksheets named like "Sheet1", "Sheet2", ..., "Sheet54", etc?

Or are you just wanting it to jump to the worksheet index that you tell it to? (Note, the worksheet index does not necessarily mean the order they are in the tabs)

 

[kpark91]

Try this
Put this in your "sheet1" module

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column = 1 And Target.Row = 1 Then Worksheets("Sheet" & Target.Value).Select
End Sub

 

[Norie]

Colin

You could try this in the Sheet1 module.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$A$1" Then
         Application.Goto Worksheets(CStr(Target.Value)).Range("A1"), True
    End If
End Sub

 

[kpark91]

Ahhh .Address.... didn't know that :P

learning!! xD

 

[Norie]

kpark

It's perhaps more common to use Column and Row, or Intersect to check that Target is located within the range of interest.

Especially if you are dealing with multiple cells/ranges.