Issue with Worksheet_DoubleClick VBA Formula

Rob W

New Member
Joined
Feb 24, 2022
Messages
4
Office Version
  1. 365
Platform
  1. Windows
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:

VBA Code:
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!
 

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

StephenCrump

MrExcel MVP
Joined
Sep 18, 2013
Messages
4,996
Office Version
  1. 365
Platform
  1. Windows
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?
 
Solution

Rob W

New Member
Joined
Feb 24, 2022
Messages
4
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Feb 24, 2022
Messages
4
Office Version
  1. 365
Platform
  1. Windows
Works perfectly now, thanks Stephen!
 

StephenCrump

MrExcel MVP
Joined
Sep 18, 2013
Messages
4,996
Office Version
  1. 365
Platform
  1. Windows
Good to hear, thanks.

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

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

Forum statistics

Threads
1,176,078
Messages
5,901,271
Members
434,884
Latest member
ares0027

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top