View original (French)

Activate a sheet on double click of a cell

Solved
Walles Posted messages 15 Status Member -  
Walles Posted messages 15 Status Member -
Hello everyone (sorry for the accents but I have a QWERTY keyboard...),

I would like to open a sheet in my Excel file by double-clicking on a cell.

The goal would be to have text-containing cells on the first page, and when double-clicking on a cell, it would open the page with the same name. I've managed to achieve this more or less, but in a very rudimentary and non-evolving way.

Here's an example:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

If Target.Value = ("A") Then
Sheets("A").Activate
End If

If Target.Value = ("B") Then
Sheets("B").Activate
End If

If Target.Value = ("C") Then
Sheets("C").Activate
End If

If Target.Value = ("D") Then
Sheets("D").Activate
End If

End Sub

I tried to create loops, declare variables, but nothing works, this is the only thing that functions for now.

Additionally, this file will be evolving, so if we need to add something, we fill in a new cell, create the sheet with the same name, and have the link established automatically.

I hope I have been clear, do not hesitate to ask if you need more information.

Thank you in advance.

3 answers

  1.
    Gyrus Posted messages 3360 Status Member 526
     
    Hello,

    Try with 
    Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    Dim Ws As Worksheet
     For Each Ws In Worksheets
     If Ws.Name = Target.Value Then
     Sheets(Target.Value).Activate
     Exit For
     End If
     Next Ws
    End Sub

    Cheers
    1
  2. ccm81 Posted messages 11033 Status Member 2 434
     
    Hello

    You have the hyperlinks that do this without using VBA
    Otherwise, try this (for a double click in the range B2:B5) 

    Const range = "B2:B5"

    Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    If Not Intersect(Target, Range(range)) Is Nothing Then
     Sheets(Target.Value).Select
    End If
    End Sub

    Best regards
    0
  3. Walles Posted messages 15 Status Member
     
    Thank you very much, indeed it works very well!
    0