Link an item to a worksheet

  • Thread starter Thread starter ERIC
  • Start date Start date
E

ERIC

I have a problem on below:

Main sheet: I have Item A,B,C
Whereby A,B,C refer to individual worksheet.
when i click on the cell written "A" ,it shall show me
worksheet "A" and so on . i have around 30- 50 items which
i intend to make a list in main sheet. It is difficult to
browse through the tab to find the sheet i want.

Hope you can help~~!
 
If I understand your problem, you could use a double click event for this.
Right click on the sheet tab>view code>copy/paste this>modify to suit. ( It
works on a worksheet name typed into a cell that you double click)

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)
Application.DisplayAlerts = False
Dim WantedSheet As String
WantedSheet = Trim(ActiveCell.Value)
If WantedSheet = "" Then Exit Sub
On Error Resume Next
If Sheets(ActiveCell.Value) Is Nothing Then
GetWorkbook ' calls another macro to do that
Else
Sheets(ActiveCell.Value).Select
ActiveSheet.Range("a4").Select
End If
Application.DisplayAlerts = True
End Sub
 
Eric,

Open the VB editor, select the sheet you want to use as your "active
directory" and paste the following 2 lines of code in the
Worksheet_BeforeDoubleClick event:

sht = ActiveCell.Value
Worksheets(sht).Activate

This done, all it takes to be taken to the desired sheet is a double click
on the cell containing its name.

HTH,
Nikos
 
Back
Top