BeforeDoubleClick worksheet event on multiple ranges

[cocacrave]

Hi,
I have this so far:

Please Login or Register  to view this content.

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...

Please Login or Register  to view this content.

[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:

Please Login or Register  to view this content.

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

Please Login or Register  to view this content.

[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.

Please Login or Register  to view this content.

... 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.