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

GBagley

Board Regular
Joined
Feb 8, 2011
Messages
84
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!
 
Just remembered, I ocasionally get a "Not Enough System Resources to Display Completely" message when I first open the workbook. But then when I click ok, everything seems to display and function fine, so I've been ignoring it. Could this have something to do with it?
 
Upvote 0

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
Try this code here is the link where I found it http://www.freevbcode.com/code/FreeMemory.bas

Code:
Attribute VB_Name = "FreeMemory"Option ExplicitPrivate Type MEMORYSTATUS    dwLength As Long    dwMemoryLoad As Long    dwTotalPhys As Long    dwAvailPhys As Long    dwTotalPageFile As Long    dwAvailPageFile As Long    dwTotalVirtual As Long    dwAvailVirtual As LongEnd TypePrivate pUdtMemStatus As MEMORYSTATUSPrivate Declare Sub GlobalMemoryStatus Lib _"kernel32" (lpBuffer As MEMORYSTATUS)Public Function AvailablePhysicalMemory() As Double'Return Value in Megabytes    Dim dblAns As Double    GlobalMemoryStatus pUdtMemStatus    dblAns = pUdtMemStatus.dwAvailPhys    AvailablePhysicalMemory = BytesToMegabytes(dblAns)    End FunctionPublic Function TotalPhysicalMemory() As Double'Return Value in Megabytes    Dim dblAns As Double    GlobalMemoryStatus pUdtMemStatus    dblAns = pUdtMemStatus.dwTotalPhys    TotalPhysicalMemory = BytesToMegabytes(dblAns)End FunctionPublic Function PercentMemoryFree() As Double   PercentMemoryFree = Format(AvailableMemory / TotalMemory * _   100, "0#")End FunctionPublic Function AvailablePageFile() As Double'Return Value in Megabytes    Dim dblAns As Double    GlobalMemoryStatus pUdtMemStatus    dblAns = pUdtMemStatus.dwAvailPageFile    AvailablePageFile = BytesToMegabytes(dblAns)End FunctionPublic Function PageFileSize() As Double'Return Value in Megabytes    Dim dblAns As Double    GlobalMemoryStatus pUdtMemStatus    dblAns = pUdtMemStatus.dwTotalPageFile    PageFileSize = BytesToMegabytes(dblAns)End FunctionPublic Function AvailableMemory() As Double'Return Value in Megabytes     AvailableMemory = AvailablePhysicalMemory + AvailablePageFileEnd FunctionPublic Function TotalMemory() As Double'Return Value in Megabytes    TotalMemory = PageFileSize + TotalPhysicalMemoryEnd FunctionPrivate Function BytesToMegabytes(Bytes As Double) As Double   Dim dblAns As Double  dblAns = (Bytes / 1024) / 1024  BytesToMegabytes = Format(dblAns, "###,###,##0.00")  End Function</PRE>
 
Upvote 0
ohhh code posted weird - just use the link

Thanks for the tip, I'll try this. Do I put this in a Module? I get a compile: syntax error on:

Attribute VB_Name = "FreeMemory"

It looks like these are just a bunch of functions with no sub to run them? Do I just go through each one manually?
 
Upvote 0
The link actually pulls up a module (bas), so copy it to your machine (save as something.bas) and then import the module into your project

you can call each function as required
 
Upvote 0
The link actually pulls up a module (bas), so copy it to your machine (save as something.bas) and then import the module into your project

you can call each function as required

Ah, ok. Got it. Of course now I can't get the bug to happen, but I'm sure it will eventually and I'll report back when it does :)
 
Upvote 0
Got it to happen again. None of the free memory functions helped :(

While it's in this weird state, I pulled up Task Manager.

CPU Usage: 0%
Physical Memory: 57%
Excel as a process (EXCEL.EXE*32) shows 433,952k memory

When I run an application.inputbox to free it up, nothing really changes with processor / memory use.
 
Upvote 0
Bumping this back up again. Still having the problem. I feel like it must be a memory problem of some sort, but I have no idea what to do about it. It's a large workbook (~70mb) with a ton of data. The main sheet is usually zoomed out to 10%, since it's being used as a plotted visualization (~25,000 cells with values and filled shading), and that's the best way to view everything.

I also notice that when I first open the workbook, everything runs fine for a while. Then once it starts locking up, I'll clear it with my workaround (calling an application.inputbox) and then all will be fine until I run the macro again. Once it locks up the first time, it seems to be more likely to lock up again after every subsequent run (making me thing memory has something to do with it). Perfmon shows 83% used physical memory at the moment, with Excel using 308,000kb
 
Upvote 0

Forum statistics

Threads
1,213,532
Messages
6,114,177
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top