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]

Here, this is how I understood your goal but note that it will work only with destination cella with one letter column coordinate (on the other hand, like yours):

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 Not IsNumeric(Mid(cellValue, i, 1)) Then
            If IsNumeric(Mid(cellValue, i, 1)) Then '<- changed
                'sheetName = Left(cellValue, i - 1)
                'cellNumber = Mid(cellValue, i)
                sheetName = Left(cellValue, i - 2) '<- changed
                cellNumber = Mid(cellValue, i - 1) '<- changed
                Exit For
            End If
        Next i
        'Debug.Print sheetName & " " & cellNumber     'check result in Immediate pane
        ' Try to activate the sheet directly
        On Error Resume Next
        ' Set ws = Sheets(Left(sheetName, WorksheetFunction.Search("B", sheetName) - 1))
        Set ws = Sheets(sheetName)                '<- changed
        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         '<- not used
                ' Go to the specified sheet and cell
                ws.Activate
                On Error Resume Next              '<- added
                ws.Range(cellNumber).Select
                On Error GoTo 0                   '<- added
            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                                        '<- not used
End Sub

 

[howard]

Many thanks for the help. This is exactly what I was looking for

 

[rollis13]

By the way, in column G, why do you need to merge Sheet Name and Cell Name?
Thanks for the positive feedback, glad having been of some help.

 

[howard]

I set up a Macro so that where there is a variance on the various sheets, it will show the sheet name followed by the cell number in Col G

 

[rollis13]

That's okay, but what I would've liked to know was what happens if you need to jump to, let's say, AB25, a double letter column.

 

[mumps]

Try:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    Dim sheetName As String, cellNumber As String, i As Long
    If Target.CountLarge > 1 Then Exit Sub
    If Target.Column <> 7 Then Exit Sub
    Application.ScreenUpdating = False
    For i = 1 To Len(Target)
        If IsNumeric(Mid(Target, i, 1)) Then
            sheetName = Left(Target, i - 2)
            cellNumber = Mid(Target, i - 1)
            Exit For
        End If
    Next i
    If Evaluate("isref('" & sheetName & "'!A1)") Then
        Sheets(sheetName).Activate
        ActiveSheet.Range(cellNumber).Select
    Else
        MsgBox "Error: Sheet not found!"
    End If
    Cancel = True
    Application.ScreenUpdating = True
End Sub

 

[howard]

Thanks for your solution mumps

 

[howard]

Rollis13, I only have cells with a single letter column for my requirements

 

[mumps]

You are very welcome.