Activate a sheet on double click of a cell
Solved
Walles
Posted messages
15
Status
Membre
-
Walles Posted messages 15 Status Membre -
Walles Posted messages 15 Status Membre -
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.
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 réponses
Hello,
Try with
Cheers
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
Hello
You have the hyperlinks that do this without using VBA
Otherwise, try this (for a double click in the range B2:B5)
Best regards
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