Get Sheet - Need A Little More

[NX555V]

Hello - I have the code below and would like the dialog box to take the value of the current selected cell.


Example: cell A1 (selected, active) says "Price Sheet Client 1" that sheet name ("Price Sheet Client 1") would auto fill into the pop up box and then the user can just hit OK to go to that sheet.


Or is there a better way to do this via VBA? My goal is that from a list of sheet names on Sheet 1 - the user can easily jump to the corresponding sheet.


Sub GetSheet()

Dim SearchData As String

SearchData = InputBox("Enter the Client Name.")

If SearchData <> vbNullString Then
On Error Resume Next
Sheets(SearchData).Activate
If Err.Number <> 0 Then MsgBox "Unable to find sheet named: " & SearchData
On Error GoTo 0
End If

End Sub


Thanks for your help.

 

[My Aswer Is This]

Assuming your sheet names will all be in column A
Try this:

When you double click on any cell in column A you will be taken to that sheet.

So if in A4 you have "Alpha" entered and you double click on Alpha you will be taken to a sheet named Alpha

This is an auto sheet event script
Your Workbook must be Macro enabled
To install this code:
Right-click on the sheet tab
Select View Code from the pop-up context menu
Paste the code in the VBA edit window

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
'Modified  5/21/2019  11:40:15 PM  EDT
If Target.Column = 1 Then
Cancel = True
On Error GoTo M
Sheets(Target.Value).Activate
End If
Exit Sub
M:
MsgBox "That sheet name does not exist"
End Sub

 

[NX555V]

Assuming your sheet names will all be in column A
Try this:

When you double click on any cell in column A you will be taken to that sheet.

So if in A4 you have "Alpha" entered and you double click on Alpha you will be taken to a sheet named Alpha

This is an auto sheet event script
Your Workbook must be Macro enabled
To install this code:
Right-click on the sheet tab
Select View Code from the pop-up context menu
Paste the code in the VBA edit window

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
'Modified  5/21/2019  11:40:15 PM  EDT
If Target.Column = 1 Then
Cancel = True
On Error GoTo M
Sheets(Target.Value).Activate
End If
Exit Sub
M:
MsgBox "That sheet name does not exist"
End Sub

Thank you very much. Curious, is there a way to make the target a range? Meaning if I double click on any cell in A1:A10 as opposed to the entire column. Thanks

 

[My Aswer Is This]

Try this:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
'Modified  5/22/2019  12:55:45 AM  EDT
If Not Intersect(Target, Range("A1:A10")) Is Nothing Then
Cancel = True
On Error GoTo M
Sheets(Target.Value).Activate
End If
Exit Sub
M:
MsgBox "That sheet name does not exist"
End Sub