Results 1 to 14 of 14
  1. #1
    Plutonium Lounger Leif's Avatar
    Join Date
    Dec 2000
    Location
    U.K.
    Posts
    14,010
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Flush mouse-click buffer (2000+)

    I have a macro that runs when a button is clicked. During that time - 2-3 seconds - the pointer turns into an hourglass then, at completion of the macro, the pointer returns to being it's normal self.

    However, if another click occurs during the running of the macro (with the hourglass over the button), the macro runs again. I'm assuming there is some kind of buffer holding the clicks - is there a practical way to empty/flush it at the end of the macro?

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Flush mouse-click buffer (2000+)

    You could also prevent the macro from running again by setting a static boolean inside the sub?
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Flush mouse-click buffer (2000+)

    Even better: disable the button during macro execution.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  4. #4
    Plutonium Lounger Leif's Avatar
    Join Date
    Dec 2000
    Location
    U.K.
    Posts
    14,010
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Flush mouse-click buffer (2000+)

    Ah! - now that will work well - thank you. It's always the simple things I overlook!

    Could you expand on the static boolean bit? - I can't quite get my head round how that would work. (I've got another application with a shape that triggers a macro which could do with similar treatment. It is a shape over a cell as I don't think I can get a button small enough and still have readable text on it.)

  5. #5
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Flush mouse-click buffer (2000+)

    Have you tried setting <code>application.interactive = false</code> and then setting it back to true at the end?
    Regards,
    Rory

    Microsoft MVP - Excel

  6. #6
    Plutonium Lounger Leif's Avatar
    Join Date
    Dec 2000
    Location
    U.K.
    Posts
    14,010
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Flush mouse-click buffer (2000+)

    No. You hadn't suggested it before <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

    That seems to work OK with command buttons but not, for some reason, with the macro-assigned shape. I need to do some more investigating...

  7. #7
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Flush mouse-click buffer (2000+)

    In the button/shape's macro:

    Sub ButtonMacro()
    Static bCalledBefore as Boolean
    If bCalledBefore Then Exit Sub
    bCalledBefore=True
    '....
    End Sub
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  8. #8
    Plutonium Lounger Leif's Avatar
    Join Date
    Dec 2000
    Location
    U.K.
    Posts
    14,010
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Flush mouse-click buffer (2000+)

    Ah, I see what you mean. But then the macro won't run a second time if required. (At least, not for me!)

    Under other circumstances, the whole issue probably wouldn't matter. In this case, the macro is sending a command (via RS232 and possibly a modem so acknowledgment return time is variable) to toggle some equipment off and on. If the user clicks twice, the second click cancels the action of the first, which might not be what is wanted!

    I have fiddled about to get a workable solution which is to log the time the macro completes, then checks at the beginning of the macro that now > completion time. This means a minimum repeat interval of about 1 second which is not an issue.

    It would be a lot simpler if I could get (variable) text onto a button the size of a cell, but that doesn't seem feasible!

    Thanks for your help!
    Attached Images Attached Images
    • File Type: gif x.gif (2.5 KB, 1 views)

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Flush mouse-click buffer (2000+)

    You can reset bCalledBefore immediately above the End Sub:

    bCalledBefore = False

    That way, the macro can be called more than once.

  10. #10
    Plutonium Lounger Leif's Avatar
    Join Date
    Dec 2000
    Location
    U.K.
    Posts
    14,010
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Flush mouse-click buffer (2000+)

    (I could have sworn I made some lengthy reply yesterday, but...)

    Nope, that doesn't (appear to) solve the problem of it running again if the shape is clicked again. I think I'll stick with the 'timed' method I have above as the slight delay before enabling a re-run has some benefits!

    ~~~

    Incidentally, I came across a neat method to add a rectangular Autoshape that fits a cell exactly:
    Click on the cell you want the shape in
    Click on Shadow Style (from the Drawing toolbar) and select any shadow
    Click on Shadow Style again and select 'No shadow'
    You then have a matching rectangle which defaults to 'Move and size with cells' to which you can then assign a macro. If you want it invisible, simply set the Line colour to 'No Line', so giving the appearance of clicking the cell to run the macro.

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Flush mouse-click buffer (2000+)

    That's handy. It also works with a selecting consisting of multiple cells.

    Another way to create a shape that fits a cell is to hold down Alt while drawing the shape. The borders will automatically be aligned with the nearest cell border.

  12. #12
    Plutonium Lounger Leif's Avatar
    Join Date
    Dec 2000
    Location
    U.K.
    Posts
    14,010
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Flush mouse-click buffer (2000+)

    That's easier!

    And, I note, if you hold down shift, it keeps the X and Y dimensions the same. Never knew that!

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Flush mouse-click buffer (2000+)

    Yep - nice if you want to draw a square, circle, equilateral triangle etc.

  14. #14
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Flush mouse-click buffer (2000+)

    Disabling the button at the start and enabling it at the end also works reliably to avoid the button reacting to clicks that were done during the processing.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

Posting Permissions

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