Double Click Cell to cell to Sheet Name and Cell

[howard]

I have sheet names in Col G2 onward followed by the cell number eg BRB85, SouthC91 etc. BR is the sheet name and B85 is cell number, South is sheet name and C91 is the ell number

I have tried to write code, so that when I double click any cell with Data in Col G from row2 onward, it will take be to the sheet name and dell number

I get message sheet not found, but Sheet does Exist

Kindly amend my code

 Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    Dim cellValue As String
    Dim sheetName As String
    Dim cellNumber As String
    Dim i As Integer
    Dim ws As Worksheet

    ' Check if double-clicked cell is in column G and not blank
    If Target.Column = 7 And Target.Value <> "" Then
        cellValue = Target.Value

        ' Extract sheet name and cell number
        For i = 1 To Len(cellValue)
            If Not IsNumeric(Mid(cellValue, i, 1)) Then
                sheetName = Left(cellValue, i - 1)
                cellNumber = Mid(cellValue, i)
                Exit For
            End If
        Next i

        ' Try to activate the sheet directly
        On Error Resume Next
        Set ws = Sheets(Left(sheetName, WorksheetFunction.Search("B", sheetName) - 1))
        On Error GoTo 0

        ' Check if sheet activation was successful
        If Not ws Is Nothing Then
            ' Check if cell number is valid
            If IsNumeric(cellNumber) Then
                ' Go to the specified sheet and cell
                ws.Activate
                ws.Range(cellNumber).Select
            Else
                ' Invalid cell number
                MsgBox "Error: Invalid cell number!"
            End If
        Else
            ' Sheet not found
            MsgBox "Error: Sheet not found!"
        End If

        ' Cancel the default double-click action
        Cancel = True
    End If
End Sub

 

[rollis13]

Okay, just added some more error checking on cell row numbers:

Option Explicit
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    Dim cellValue As String
    Dim sheetName As String
    Dim cellNumber As String
    Dim i     As Integer
    Dim ws    As Worksheet
    'check if double-clicked cell is in column G and not blank
    If Target.Column = 7 And Target.Value <> "" Then
        cellValue = Target.Value
        'extract sheet name and cell number
        For i = 1 To Len(cellValue)
            If IsNumeric(Mid(cellValue, i, 1)) Then
                On Error Resume Next
                sheetName = Left(cellValue, i - 2)
                cellNumber = Mid(cellValue, i - 1)
                On Error GoTo 0
                Exit For
            End If
        Next i
        Debug.Print sheetName & " " & cellNumber
        'try to activate the sheet directly
        On Error Resume Next
        Set ws = Sheets(sheetName)
        On Error GoTo 0
        'check if sheet activation was successful
        If Not ws Is Nothing Then
            'check if cell number is valid
            If Mid(cellNumber, 2) <= Rows.Count Then
                'go to the specified sheet and cell
                ws.Activate
                ws.Range(cellNumber).Select
            Else
                'invalid cell number
                MsgBox "Error: Invalid cell number !"
            End If
        Else
            'sheet not found
            MsgBox "Error: Sheet not found !"
        End If
        'cancel the default double-click action
        Cancel = True
    End If
End Sub

 

[howard]

Thanks very much rollis13