Thanks Thanks:  0
Likes Likes:  0
Page 1 of 4 123 ... LastLast
Results 1 to 10 of 33

Thread: Issue with excel locking up, mouse clicks not working, duplicate keypress events

  1. #1
    Board Regular
    Join Date
    Feb 2011
    Posts
    84
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Issue with excel locking up, mouse clicks not working, duplicate keypress events

    Posted this on the Ozgrid forums, but haven't gotten any help yet, so I thought I'd try here too!

    I've been having a strange problem lately. I have a fairly lengthy macro that works perfectly most of the time. Occasionally it will run as expected but as soon as the macro ends, excel becomes unresponsive to mouse-clicks. When I click anywhere (trying to select a cell, or an excel menu item...clicking anywhere in excel) I'll get the a 'ding' system sound and nothing will happen. BUT, if I use the keyboard arrow keys, I can see that the active cell selection moves accordingly. Then it gets really strange - when I have a cell highlighted and press any key to input text, it gets duplicated. So if I press "s' it will input "ss" into the cell, and then when I press enter it will auto-move to the next cell down, but nothing ends up getting saved into the previous cell.

    I don't understand what's going on at all. I can't think of anything in my macro that would have these kinds of effects. I've made sure that screenupdating is turned back on at the end of every procedure. If I go into the VBE, I can manually run procedures and they all work fine. The only way I've been able to get back to normal is by force closing excel altogether and re-opening. Any ideas?

    ______________________

    Still having trouble, and the same thing is happening with this workbook on two different computers, so I don't think it's a hardware, or OS specific issue. When it gets locked up like this, I can still do anything in the VBE (edits cells, run macros, etc.) with no problems. If I'm in the excel window, I can click alt on the keyboard and the shortcut keys for the menu come up, but I can't go deeper than that by clicking the letter shortcuts, they do nothing. If I use the delete key to delete the contents of a cell, then it gets deleted. But if I type anything else (numbers, letters, or symbols) then it types 2 instances of the key every time, yet when I hit enter, nothing changes in the cell. I also can't really bring focus to the excel window if something else (ie. the VBE) is on top of it, clicking into the excel window just gives the little system 'ding' sound and nothing happens.

    If I hit the save button in the VBE (since I can't click anything in the excel window), then it seems to snap out of it and go back to normal. I tried searching for anything simmilar to this and can't find anything...

    Any help would be much appreciated. This is driving me nuts!

  2. #2
    Board Regular Ruddles's Avatar
    Join Date
    Aug 2010
    Location
    Glevum Castra, Britannia
    Posts
    5,770
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Issue with excel locking up, mouse clicks not working, duplicate keypress events

    (To save anyone else searching, the OzGrid thread is at http://www.ozgrid.com/forum/showthread.php?t=150538. No responses yet.)

    My first impression was that you may have been tying up machine resources by being locked in a loop where the exit condition wasn't being satisfied, or something like a Worksheet_Change macro recursively triggering itself. But you say you can return to the VBE and it appears to be semi-responsive? Does the 'stop' button react to being clicked? How about Ctrl-Break?

    The only thing I can think of is a problem with event handlers, Application.OnKey, Application.OnTime... I think that's about it. Do you make use of any of those? (You can check whether an event handler is being called recursively by adding something like:-
    Debug.Print "Worksheet_Change starting at ";Format(Now(),"hh:nn:ss")
    as the very first statement and
    Debug.Print "Worksheet_Change exiting at ";Format(Now(),"hh:nn:ss")
    as the very last statement.)

    Finally try this: when your workbook is behaving correctly, start Task Manager and check the CPU utilisation under the Performance tab. Remember that mental picture. The next time the workbook misbehaves, check the CPU utilisation again. Does the machine appear to be much busier now than it was when the workbook was working okay?

    I'm happy to check the workbook out if you're happy to let me see it, but I'd understand if not.

    I sympathise - intermittent probems are the worst to troubleshoot!
    R.
    ---
    On two occasions I have been asked, 'Pray, Mr Babbage, if you put into the machine wrong figures, will the right answers come out?'
    I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question.

  3. #3
    Board Regular
    Join Date
    Feb 2011
    Posts
    84
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Issue with excel locking up, mouse clicks not working, duplicate keypress events

    Quote Originally Posted by Ruddles View Post
    (To save anyone else searching, the OzGrid thread is at http://www.ozgrid.com/forum/showthread.php?t=150538. No responses yet.)

    My first impression was that you may have been tying up machine resources by being locked in a loop where the exit condition wasn't being satisfied, or something like a Worksheet_Change macro recursively triggering itself. But you say you can return to the VBE and it appears to be semi-responsive? Does the 'stop' button react to being clicked? How about Ctrl-Break?

    The only thing I can think of is a problem with event handlers, Application.OnKey, Application.OnTime... I think that's about it. Do you make use of any of those? (You can check whether an event handler is being called recursively by adding something like:-
    Debug.Print "Worksheet_Change starting at ";Format(Now(),"hh:nn:ss")
    as the very first statement and
    Debug.Print "Worksheet_Change exiting at ";Format(Now(),"hh:nn:ss")
    as the very last statement.)

    Finally try this: when your workbook is behaving correctly, start Task Manager and check the CPU utilisation under the Performance tab. Remember that mental picture. The next time the workbook misbehaves, check the CPU utilisation again. Does the machine appear to be much busier now than it was when the workbook was working okay?

    I'm happy to check the workbook out if you're happy to let me see it, but I'd understand if not.

    I sympathise - intermittent probems are the worst to troubleshoot!
    The only event handlers that I have are within a userform (handlers that watch for list/button/toggle changes in the userform), but that's it, and the userform has always been unloaded when I have the problem. I've tried using the immediate window to manually unload all userforms too, which didn't help. VBE is always fine when I go into it, doesn't show that anything is running or in break mode or anything like that. Clicking the stop button doesn't help anything. I'll try checking the CPU utilization next time and messing with some debug.print options.

    I've found that the best way to get out of it is to simply run another macro. I have a quick and simple one that just clears the contents of a selected range, and when I run that from the VBE, everything returns to normal.

  4. #4
    Board Regular
    Join Date
    Feb 2011
    Posts
    84
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Issue with excel locking up, mouse clicks not working, duplicate keypress events

    Bumping this back up since I'm having the same problem again. It's very odd and I still haven't been able to figure it out. The only way I can seem to get excel back to normal is by going into VBE and running a macro with an Application.InputBox -- doesn't matter what else is in the macro, and you don't have to select anything or complete the macro, just as soon as the input box pops up, then excel is magically back to normal. I tried a standard inputbox which didn't work, and a userform with a refedit, which also didn't work.

  5. #5
    Moderator mole999's Avatar
    Join Date
    Oct 2004
    Location
    UK
    Posts
    9,117
    Post Thanks / Like
    Mentioned
    10 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Issue with excel locking up, mouse clicks not working, duplicate keypress events

    any copy paste in there,

    maybe

    Code:
    Declare Function CloseClipboard Lib "user32" () As Long
    Declare Function EmptyClipboard Lib "user32" () As Long
    Declare Function OpenClipboard Lib "user32" (ByVal hwnd As Long) As Long
    
    Sub ClearClipboard()
        OpenClipboard 0&
        EmptyClipboard
        CloseClipboard
    End Sub
    • Yes I know there are better ways to do it. I just wish I knew them. - 2003, 2007, 2010, 2013 & 2016
    • I wear my ignorance openly, excel is not my chosen career, its a means to an ends
    • Posting Guidelines Want to post well laid out questions and answers Translate Excel Versions
      Code:
      [CODE ]Put Your Code[/ CODE]
    • Settings > General Settings (on the left) scroll to the bottom, > Miscellaneous Options > Use ENHANCED
    • X-Posting Guidelines Rule 13 > CHART STUFF

  6. #6
    Board Regular
    Join Date
    Feb 2011
    Posts
    84
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Issue with excel locking up, mouse clicks not working, duplicate keypress events

    Quote Originally Posted by mole999 View Post
    any copy paste in there,

    maybe

    Code:
    Declare Function CloseClipboard Lib "user32" () As Long
    Declare Function EmptyClipboard Lib "user32" () As Long
    Declare Function OpenClipboard Lib "user32" (ByVal hwnd As Long) As Long
    
    Sub ClearClipboard()
        OpenClipboard 0&
        EmptyClipboard
        CloseClipboard
    End Sub
    Nope, that didn't help. I don't think I'm doing anything with the clipboard in this particular program.

    When it gets locked up and weird, I can still use the VBE with no issues (the immediate window too) - is there anything you can think to check? Current states of the excel application that I can check using a msgbox? Application.ScreenUpdating is set to True, and I tried toggling it to false and then back to true, which didn't help.

  7. #7
    Moderator mole999's Avatar
    Join Date
    Oct 2004
    Location
    UK
    Posts
    9,117
    Post Thanks / Like
    Mentioned
    10 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Issue with excel locking up, mouse clicks not working, duplicate keypress events

    I was thinking its outside of excel, when you get key repeats etc, I've often seen that with computer keyboard buffer being full (hardware type issue)

    I guess you've checked on another system and get the same problem

    I've also seen on here, suggesting a save after code
    • Yes I know there are better ways to do it. I just wish I knew them. - 2003, 2007, 2010, 2013 & 2016
    • I wear my ignorance openly, excel is not my chosen career, its a means to an ends
    • Posting Guidelines Want to post well laid out questions and answers Translate Excel Versions
      Code:
      [CODE ]Put Your Code[/ CODE]
    • Settings > General Settings (on the left) scroll to the bottom, > Miscellaneous Options > Use ENHANCED
    • X-Posting Guidelines Rule 13 > CHART STUFF

  8. #8
    Board Regular
    Join Date
    Feb 2011
    Posts
    84
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Issue with excel locking up, mouse clicks not working, duplicate keypress events

    Yes, same problem on a few different computers. It's odd because I only get the duplicate keypress events in excel. All other applications work perfectly fine when this is happening. I can still use any excel keyboard shortcuts with no issues too, though none of them seem to help the program snap out of it.

    Anyways, it seems that the most reliable way to fix it at this point is to just always bring up an application.inputbox (type:=8) at the end of my code. Is there a way to bring up the inputbox and then immediately close it without requiring input from the end user? I know you can do this with custom userforms (can easily hide them too), but those don't solve my problem...just the application.inputbox does.

    Saving the file works as well, but it's a large file that takes some time to fully save, so I would rather not do that after every execution.

  9. #9
    Board Regular
    Join Date
    Feb 2011
    Location
    Brampton, Ontario
    Posts
    498
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Issue with excel locking up, mouse clicks not working, duplicate keypress events

    this link may have some answers

    http://www.ozgrid.com/VBA/SpeedingUpVBACode.htm
    xl2007 - Windows-7 & XP

    Does xl hate the number 255 ?
    biggest limitation - drives me insane

  10. #10
    Board Regular
    Join Date
    Feb 2011
    Posts
    84
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Issue with excel locking up, mouse clicks not working, duplicate keypress events

    Quote Originally Posted by Rasm View Post
    this link may have some answers

    http://www.ozgrid.com/VBA/SpeedingUpVBACode.htm
    Nope. It's not a speed problem, as my macro works great, up until it ends and locks up like this. I tried checking for any open userforms and unloading any that were open - no luck there. ScreenUpdating is always reset back to True before the macro ends, and EnableEvents (which I never change in this program) is still true. According to VBE at least, the macro has finished running - no errors or breaks.

Some videos you may like

User Tag List

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
  •