Activate sheet when clicking on cell based on cell value-CodePudding

I was attempting to set up a way to jump to sheets in the same workbook by clicking on a cell and activating the sheet with the same value. My code works when the cell value is text. However, it does not work when the value is a number. The majority (99%) of the sheets will be only numbers (6 digits) for the sheet name. An example of a sheet name would be 110110. Here is my code I used:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.ScreenUpdating = False
If Selection.Count = 1 Then
If Not Intersect(Target, Range("B4:B800")) Is Nothing Then
If Target.Value <> "" Then
Sheets(Target.Value).Activate
End If
End If
End If
End Sub

I receive: Run-time error '9':

Subscript out of range

whenever I click on the

CodePudding user response：

In Excel, you can refer to a sheet by the name or index.

In your case, you are referring by index (number) that does not exist in the Excel. You can simple convert the number to string by using CStr():

Sheets(CStr(Target.Value)).Activate