Results 1 to 8 of 8

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

  1. #1
    New Member
    Join Date
    May 2018
    Location
    Paso Robles, CA
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

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

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

  2. #2
    MrExcel MVP
    Moderator
    Jerry Sullivan's Avatar
    Join Date
    Mar 2010
    Location
    San Diego, California
    Posts
    8,787
    Post Thanks / Like
    Mentioned
    11 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Call to doubleclick works on one computer, not another....

    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.
    Last edited by Jerry Sullivan; May 31st, 2018 at 12:24 AM.
    Using Excel 2016

  3. #3
    New Member
    Join Date
    May 2018
    Location
    Paso Robles, CA
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Call to doubleclick works on one computer, not another....

    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



  4. #4
    New Member
    Join Date
    May 2018
    Location
    Paso Robles, CA
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Call to doubleclick works on one computer, not another....

    sorry - should show at least the basic code:

    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
    

  5. #5
    MrExcel MVP
    Moderator
    Jerry Sullivan's Avatar
    Join Date
    Mar 2010
    Location
    San Diego, California
    Posts
    8,787
    Post Thanks / Like
    Mentioned
    11 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Call to doubleclick works on one computer, not another....

    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.

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

    Code:
    Public Sub Workbook_SheetBeforeDoubleClick(ByVal sh As Object, ByVal target As Range, Cancel As Boolean)
     Cancel = True
     Call InfoPopUp
    
    End Sub
    Using Excel 2016

  6. #6
    New Member
    Join Date
    May 2018
    Location
    Paso Robles, CA
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Call to doubleclick works on one computer, not another....

    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

  7. #7
    MrExcel MVP
    Moderator
    Jerry Sullivan's Avatar
    Join Date
    Mar 2010
    Location
    San Diego, California
    Posts
    8,787
    Post Thanks / Like
    Mentioned
    11 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Call to doubleclick works on one computer, not another....

    Quote Originally Posted by WT Cline View Post
    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.
    Last edited by Jerry Sullivan; Jun 3rd, 2018 at 03:46 PM.
    Using Excel 2016

  8. #8
    New Member
    Join Date
    May 2018
    Location
    Paso Robles, CA
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Call to doubleclick works on one computer, not another....

    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

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •