BeforeDoubleClick worksheet event on multiple ranges

[cocacrave]

Hi,
I have this so far:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    If Target.Address = Range("tp.A").Address Then
        Call tpSelect
        Cancel = True
    End If
    If Target.Address = Range("tp.B").Address Then
        Call tpSelect
        Cancel = True
    End If
End Sub

It works fine, but I'm sure there is a better way to write this.
There will be many more ranges (50+ ranges) and they all "Call tpSelect" on doubleclick.
I have tried "If Target.Address = Range("tp.A", "tp.B", ...).Address Then" but that doesn't work I guess.
Oh and these are named ranges...

Thanks in advance.

[Leith Ross]

Hello cocacrave,

Welcome to the Forum!

This should work...

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    If Not Intersect(Target, Range("tp.A"), Range("tp.B")) Is Nothing Then
        Call tpSelect
        Cancel = True
    End If
End Sub

[cocacrave]

I have tried your solution. Thank you for posting.
For some reason, it doesn't work.
When I double click, it goes into edit mode instead of running the macro.
Does it matter if the named ranges are merged cells?
For example, A1:C4 has a name tp.A
I have gotten it work with what I wrote above with these merged cells but it will be super long and messy.
Any ideas why it's not working?

[Leith Ross]

Hello cocacrave,

It is really difficult to diagnose the problem because I don't have a copy of your workbook. Running your code on my computer would help to isolate or eliminate potential problems.

Please redact any sensitive or confidential information before you post the workbook.

How To Post Your Workbook

1. At the bottom right of the Reply window, Click the button Go Advanced
2. At the top of the Your Message Window, in the first row of icons, Click the Paperclip icon.
3. Click the Add Files button at the top right in the dialog box. This displays the File Manager dialog.
4. Click the Select Files button at the bottom middle of the dialog.
5. In the new window Find the file you want to upload, select it, and Click Open.
6. You will now be back in the File Manager dialog. Click the bottom Middle button Upload File.
7. Wait until the file has completely uploaded before you exit the File Manager dialog.

[TMS]

Either of these will work:

Option Explicit

Private Sub xWorksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Target.Address = Range("tp.A").Address _
Or Target.Address = Range("tp.B").Address Then
    Call tpselect
    Cancel = True
End If
End Sub

' or

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Intersect(Target, _
             Union(Range("tp.A"), _
                   Range("tp.B"))) Is Nothing Then Exit Sub
    Call tpselect
    Cancel = True
End Sub

Sub tpselect()

MsgBox ActiveCell.Address

End Sub

Regards, TMS

[cocacrave]

Thank you, the first one didn't work but the second solution worked perfectly.

[cocacrave]

It turns out I can't even use your solution because of too many 24 line continuation limit which I did not even know about.
I need atleast 1600+ line continuation to write it in this format.. I guess I just have to use what I wrote which is painfully long but atleast it isnt limited.
Unless there's a workaround?

[nilem]

Hi cocacrave,
maybe so

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
On Error Resume Next
If InStr("tp.A,tp.B,tp.C", Target.Name.Name) = 0 Then Exit Sub
'"tp.A,tp.B,tp.C" - continue as you need
Call tpselect
Cancel = True
End Sub

[TMS]

You don't have to use the line extensions as I have in the examples. That was really just to show the structure of the code.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Intersect(Target, Union(Range("tp.A"), Range("tp.B"))) Is Nothing Then Exit Sub
    Call tpselect
    Cancel = True
End Sub

Sub tpselect()

MsgBox ActiveCell.Address

End Sub

... works just as well


Regards, TMS

[Norie]

What ranges do tp.A, tp.B etc refer to?

[cocacrave]

tp.A: B16-G18
tp.B: J16-O-18
... I named these and then merged them so I can use it like a button and later I can also move them around the spreadsheet without causing problems.
I have many of these buttons, in the 1600's.

Thank you TMS, it works and is much better than what I had before.

[TMS]

You're welcome.



If you are satisfied with the solution(s) provided, please mark your thread as Solved.


New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save


You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.