Issue with Worksheet_DoubleClick VBA Formula

[Rob W]

Good Afternoon,

I'm working on a macro to activate a named worksheet when a cell with the corresponding name on a separate worksheet is double-clicked on. I tried to use a macro suggested in another thread but it doesn't seem to work. For reference I am working in the O365 desktop version of Excel.

Ex. Cell Value A3 is "Paella", double click on the cell to pull up the Worksheet named "Paella"

The column to be double clicked on in a Worksheet called "Table of Contents", and the range in question would be A1:A100.

Here's the code I attempted to use with no effect:

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

Application.EnableEvents = True

If Intersect(target, Range("A:A")) Is Nothing Then Exit Sub

v = target.Value

Dim ws As Worksheet

For Each ws In Worksheets
     If ws.Name = v Then
     Cancel = True
     ws.Activate
End If

Next
End Sub

Any help is greatly appreciated.

Thanks!

 

[StephenCrump]

Welcome to the Forum!

Your code works for me ...

Have you put the Sub in the sheet module for the worksheet "Table of Contents"?

Is Application.EnableEvents = True before you double click?

 

[Rob W]

Ah that's probably the issue, the event for me was in the general userform section.
Let me see if that fixes it, thanks!

 

[Rob W]

Ah that's probably the issue, the event for me was in the general userform section.
Let me see if that fixes it, thanks!

general module*

 

[Rob W]

Works perfectly now, thanks Stephen!

 

[StephenCrump]

Good to hear, thanks.

It's not a big deal here, but in lieu of looping through all worksheets, you could just have:

On Error Resume Next
Worksheets(v).Activate
'optional next line
If Err <> 0 Then MsgBox "Worksheet " & v & " not found"
On Error GoTo 0