Call to doubleclick works on one computer, not another....

[WT Cline]

Hello - am stumped trying to determine what could be happening.

I have created some code in ThisWorkBook, capturing a double click event in a particular cell. When double clicking, this code executes exactly as designed, no issues.

I want to also be able to call this double click via a menu and from within another procedure, basically "fooling" the computer into thinking a double click occurred. I didn't want to simply copy/paste/duplicate the code in another procedure, so I created a tiny sub routine that simply calls the Doubleclick event. This works as expected on my computer, but not on another. On the second computer, I am getting the dreaded "Method or data member not found".

Public Sub Workbook_SheetBeforeDoubleClick(ByVal sh As Object, ByVal target As Range, Cancel As Boolean)

    Dim DefPath As Variant...
    
    'and a bunch of code that works on both computers.

End sub 

I call this code like this:

Public Sub InfoPopUp()

    Call ThisWorkbook.Workbook_SheetBeforeDoubleClick(ThisWorkbook, Selection, True)

End Sub

As mentioned, this works on my computer, every time. But on another computer, it stops at "Public Sub InfoPopUp" with the "Method or data member not found".,

Any ideas what I should be looking for? Or a better way to accomplish what I am trying to do?

Thanks,
Terry

 

[Jerry Sullivan]

Hi Terry,

I don't know why the behavior is inconsistent, but I notice you are passing ThisWorkbook as the Object for the sh parameter. In the typical triggering of the Workbook_SheetBeforeDoubleClick, sh is assigned the worksheet in which the user double-clicked a cell.

IMO, a more reliable approach would be to have a third procedure that performs the actions e.g. MySub. That could have the same parameters and be called from both the Workbook_SheetBeforeDoubleClick event, and your InfoPopUp Sub.

 

[WT Cline]

Thank you Jerry

I have taken your advice - which I wholeheartedly agree is the better coding practice - and moved the code from the Workbook_SheetBeforeDoubleClick event to the InfoPopUp sub routine.

I now simply call InfoPopUp from the Workbook_SheetBeforeDoubleClick event and wherever else I need to. This works...accept for one maddening issue. When I execute InfoPopUp from within the doubleclick event, the userform is displayed as expected. The form is set to modeless; the whole idea is that it can float on the screen, while the user does other stuff. When InfoPopUp is invoked from the doubleclick event, it displays, but is not responsive.

I can't click on it, can't move it. I get that annoying "ding" that happens when one clicks on some form that doesn't expect a click.

To get out of this, I find I need to click somewhere on the spreadsheet; that change of focus allows me to access the userform.

If I invoke my InfoPopUp routine from anywhere other than the doubleclick event, ie via a menu, this behavior does NOT occur.

I finally traced the issue down to the moment the code exits the doubleclick event. If one stops the code prior to this, userform can be moved or accessed as I would expect. But, once the code exits the doubleclick event (End Sub), the form is non-responsive.

Odd note: if I were to leave the userform code inside the Workbook_SheetBeforeDoubleClick event, this does not occur. Ie, the userform is responsive immediately, even after exiting the doubleclick event. Weird, huh?

I have no idea why the combination of calling a subroutine, and then exiting the Workbook_SheetBeforeDoubleClick event would suddenly lock things up. I have tried a zillion ways to trick this, using AppActivate concepts I found online, even moving the cursor around the spreadsheet using code to change focus. But all of these attempts are talking place BEFORE the code exits the doubleClick event, so have no affect.

Am I missing something obvious? Is there some sub that can be automatically called following a doubleclick event? Or, can I modify the parameters that come into the DoubleClick event in some way that would make a difference?

I may have to go back to just having the same code in two places. But that rubs my amateur programming brain the wrong way!

By the way, I still haven't solved why the original code worked on one computer and not another, the original question. My goal was first to use better programming practices, see if that solved anything. But....can't get the code to work the way I want in the first place.

Any ideas?

Many thanks for your support,

Terry

 

[WT Cline]

sorry - should show at least the basic code:

Public Sub Workbook_SheetBeforeDoubleClick (ByVal sh As Object, ByVal target as Range, Cancel as boolean)

Call InfoPopUp

End sub

Public Sub InfoPopUp()

Unload PopUpForm1
' bunch of code that gathers and displays data - this all works
PopUpform1.show

End sub

 

[Jerry Sullivan]

Your code example didn't have the vbModeless argument, but since this worked for you when called by other procedures, I suspect that got deleted in your posting.

Public Sub InfoPopUp()

 Unload PopUpform1
 ' bunch of code that gathers and displays data - this all works
 PopUpform1.Show vbModeless

End Sub

The behavior you describe is probably due to not having a statement to cancel the default action of the DoubleClick event. If you add this, it should work as you intended.

Public Sub Workbook_SheetBeforeDoubleClick(ByVal sh As Object, ByVal target As Range, Cancel As Boolean)
 Cancel = True
 Call InfoPopUp

End Sub

 

[WT Cline]

Thank you Jerry!

Adding "Cancel = True" did the trick! I guess I need to bone up on how these events REALLY work...

Regarding the modeless business, I have the form property ShowModal set to false. Is there any difference in terms of behavior or best practices going his route?

Anyway - can't thank you enough for your support.

Now, onto to seeing if any of this fixes the original issue...will find out next week.

Thanks again, brilliant!

Terry

 

[Jerry Sullivan]

Regarding the modeless business, I have the form property ShowModal set to false. Is there any difference in terms of behavior or best practices going his route?

Hi Terry,

Setting the property in the form at design time will give the same result. I think it's a little clearer to set the modeless state on the UserForm.Show call- but that just a style preference.

I'm glad that helped. Good luck testing on the other computer.

 

[WT Cline]

Hello Jerry - just wanted to pass along that all is working as expected, INCLUDING solving the original issue, where the original code worked on my computer, but not on a colleagues computer.

Thanks very much for your willingness to share your knowledge. Very much appreciated!

Terry