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!
 
FYI

If you are making charts them be carefull - I have found that Excell state you can have 32K points in chart. However when I check using

SeriesCollection(PlotCounter).Points.Count

than I find that only 2^14 or 16384 points have been plotted - even when it should be like 25K points - I get no errors - no messages or anything - but it kills your memory and does freeze up. So I now make charts that are less than 16384 - just make multiple charts. That fixed my problem.

Also if you have screenupdating set to false when you have the crash then you cannot use the mouse on the data sheet itself - only on the code part.
 
Upvote 0

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
Not a single chart in this one. ScreenUpdating is alway set to true when the crash happens (I've tried checking it's value in the VBE immediate window as well as toggling it off and then back on, no help).

Not the autoshapes issue from ZVI either. I did a count of shapes and only have 1 (I think it would be the main 'start' button that I have on the sheet).
 
Upvote 0
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?
Ignoring of this type of warning can cause such a problem.
Try to set zoom=100% for all sheets, save workbook, close Excel, reload workbook and test it.
 
Upvote 0
It's not consistent though. Usually I don't get that message, but still have the problem (or I can get that message, but not have the problem). And I'm still puzzled by why calling up an inputbox would be the only way to get things back to normal.
 
Upvote 0
It's not consistent though. Usually I don't get that message, but still have the problem (or I can get that message, but not have the problem). And I'm still puzzled by why calling up an inputbox would be the only way to get things back to normal.
May be, but please try the recommendation because it's the one of the possible reason even without getting of the warning message.

It's hard to guess the exact reason without workbook example.
Could you inform what of the listed below are used:
1. RefEdit control in the userform
2. Conditional Formatting of the cells
3. Application.InputBox Type:=8 in the code
 
Upvote 0
May be, but please try the recommendation because it's the one of the possible reason even without getting of the warning message.

It's hard to guess the exact reason without workbook example.
Could you inform what of the listed below are used:
1. RefEdit control in the userform
2. Conditional Formatting of the cells
3. Application.InputBox Type:=8 in the code

I'll give it a try. It's hard to use the program without being zoomed out quite a ways though, due to the size of the sheet and how much you need to see at a time.


I have 3 different RefEdit controls in the userform. Only 1 is ever used at a time (depending on other options that are chosen for the program). I thought the problem might have something to do with what has focus, since when this problem is happening if I click on the sheet it doesn't respond and I get the little system 'ding' sound, like there's a message that needs to be dismissed before you can get back to the excel sheet, but nothing I've tried with this in mind has worked (manually checking / closing all userforms, set focus to excel, check for other open windows or errors...etc). VBE always works fine and I can still manipulate excel with the immediate window or run my macros from there.

I have conditional formatting applied to the entire main sheet (shading any cell that is empty a light grey). This isn't necessary if it could be causing the problem, it just makes the sheet look nicer and was the easiest way to get all the blank cells shaded.

Application.InputBox Type:=8 -- bringing this up and then dismissing it is what will always, without fail, bring the sheet back to normal when it starts locking up and being weird. I'm currently only using it in a simple 'debug' module for that purpose. All other range selections for the code are done with one of the two RefEdit boxes in the main userform. Bringing up a simple refedit box doesn't solve the problem, just the application.inputbox type:=8.
 
Upvote 0
Though you don’t use Application.InputBox in the code I shall warn against usage of Application.InputBox Type:=8 if the formulas are used in Conditional Formatting (CF).
In this case use the direct reference(s) of CF to the aux cell(s), where the conditional formulas are stored.
Using of RefEdit can help, but please have a look on its disadvantages in the links below.

BTW, irregularity occurs also for interaction of formulas in CF and Application.GetOpenFileName at MultiSelect:=True.

Depending of the particular code the adding of DoEvents to the user form code with RefEdit can help.

The links to additional info:
Odd refresh behavior after you specify a conditional format in Excel
RefEdit Irregularities
Alternative to Excel's Flaky RefEdit Control

Below is one more alternative to RefEdit usage.
UserForm1 with TextBox1 is loaded in modeless mode by the aid of such code:
Rich (BB code):

' Code of Module1
Sub FormShow()
  UserForm1.Show 0
End Sub

It is possible to change selection in modeless mode
The changing of selection will automatically cause the changing of Textbox1 value.
The code of UserForm1:
Rich (BB code):

' Code of (modeless) UseForm1
' TextBox1 is used for auto entering the selection's address
Dim WithEvents App As Application

Private Sub App_SheetActivate(ByVal Sh As Object)
  RefreshTextBox1
End Sub

Private Sub App_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
  RefreshTextBox1
End Sub

Private Sub App_WorkbookActivate(ByVal Wb As Workbook)
  RefreshTextBox1
End Sub

Private Sub UserForm_Activate()
  Set App = Application
  RefreshTextBox1
End Sub

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
  Set App = Nothing
End Sub

' Put address of selection into TextBox1
Sub RefreshTextBox1()
  TextBox1 = "'" & Selection.Parent.Name & "'!" & Selection.Address
End Sub

To find the exact source(s) of issue try to exclude any CFs and/or replace RefEdit control by its alternative (see above links and the code). Do it with copy of workbook
There can be other reasons of issue but only example and the issue scenario can help to find & solve it.
 
Upvote 0
Though you don’t use Application.InputBox in the code I shall warn against usage of Application.InputBox Type:=8 if the formulas are used in Conditional Formatting (CF).
In this case use the direct reference(s) of CF to the aux cell(s), where the conditional formulas are stored.
Using of RefEdit can help, but please have a look on its disadvantages in the links below.

BTW, irregularity occurs also for interaction of formulas in CF and Application.GetOpenFileName at MultiSelect:=True.

Depending of the particular code the adding of DoEvents to the user form code with RefEdit can help.

The links to additional info:
Odd refresh behavior after you specify a conditional format in Excel
RefEdit Irregularities
Alternative to Excel's Flaky RefEdit Control

Below is one more alternative to RefEdit usage.
UserForm1 with TextBox1 is loaded in modeless mode by the aid of such code:
Rich (BB code):

' Code of Module1
Sub FormShow()
  UserForm1.Show 0
End Sub

It is possible to change selection in modeless mode
The changing of selection will automatically cause the changing of Textbox1 value.
The code of UserForm1:
Rich (BB code):

' Code of (modeless) UseForm1
' TextBox1 is used for auto entering the selection's address
Dim WithEvents App As Application

Private Sub App_SheetActivate(ByVal Sh As Object)
  RefreshTextBox1
End Sub

Private Sub App_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
  RefreshTextBox1
End Sub

Private Sub App_WorkbookActivate(ByVal Wb As Workbook)
  RefreshTextBox1
End Sub

Private Sub UserForm_Activate()
  Set App = Application
  RefreshTextBox1
End Sub

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
  Set App = Nothing
End Sub

' Put address of selection into TextBox1
Sub RefreshTextBox1()
  TextBox1 = "'" & Selection.Parent.Name & "'!" & Selection.Address
End Sub

To find the exact source(s) of issue try to exclude any CFs and/or replace RefEdit control by its alternative (see above links and the code). Do it with copy of workbook
There can be other reasons of issue but only example and the issue scenario can help to find & solve it.

Interesting, I'll have to read through all this and play with some things. Thanks for the tips! These in particular:

"RefEdits must be placed directly on the UserForm itself. If you put a RefEdit in a frame or on a multipage, strange things will happen, including bizarre Excel crashes."

The main RefEdit that I use is inside a frame which is inside a multipage. Hmm...


"RefEdit event procedures should be avoided. RefEdit events do not behave reliably, and they may result in VBA errors which are difficult to debug."

I have a RefEdit event procedure as well, which checks if a range has been selected, and if not, then a command button is disabled (since the refedit entry is required to run the program).
 
Upvote 0
Ok, so I set the userform to modeless, but then my userform.activate event handlers don't seem to trigger. The userform then goes unresponsive after I make a selection on the sheet, and excel seems to lock up altogether. Then when I try to force close it, I get a 'cannot quit excel' message. When I dismiss that dialog box, then the userform dissapears and I start having the same problem as my initial post, where any keypress is duplicated into the cell and I can't use the mouse to select anything, just the keyboard.

I don't know much about modal/modeless forms; is there something I'm doing wrong or missing here?

For now, I have removed the refedits from my userform and am using an Application.Inputbox Type:=8 -- No issues yet, but will see if the problem comes up again.
 
Upvote 0
Using of RefEdit in modeless form (ShowModal = False) hangs up Excel.
Use the non modeless form (ShowModal = True) or/and alternatives to RefEdit control.
Please also take into account that InputBox Type:=8 is not good if formulas are used in CFs.
 
Upvote 0

Forum statistics

Threads
1,213,568
Messages
6,114,348
Members
448,570
Latest member
rik81h

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