VBE Editor doesn't respond to mouse/keystrokes

This happens to me, intermittently, and when I restart Excel, everything works just fine.  I realize if one is editing a cell, or focused on an object in Excel, then responsiveness from the VBE Editor is NILL for obvious reasons.  However, I've checked and can select cells, use menus, etc., but when I switch windows to the VBE Editor, and try to type, or click something, all I get is a "bing" sound from the computer.  No response.

Any ideas how to "clear" the problem without punting?

Dave
LVL 42
dlmilleAsked:
Who is Participating?
 
broro183Connect With a Mentor Commented:
Hi Dave,

These are just some random shots in the dark...
- Do you have any code that registers "hot keys" in the VBE?
For example, Stephen Bullen's http://www.oaltd.co.uk/DLCount/DLCount.asp?file=VBEOnKey.zip which is available from http://www.oaltd.co.uk/Excel/Default.htm 

- This is very unlikely, but anyway... check out this link ( http://www.mrexcel.com/forum/showpost.php?p=138317&postcount=6 ) to some code of Ivan Maola's. Can you make use of his "EnableGettingIntoVBE" sub?

- Do you have any modeless (& hidden) forms that are automatically opened?

- Do you have multiple VBE addins open such as MZTools & Smart Indenter?
- If so, does anything change when you close one of them (or stop it loading)?

Does Performance Monitor or Process Monitor (available from http://technet.microsoft.com/en-us/sysinternals/bb795533 ) provide any better detail than the standard Task Manager?


hth
Rob
0
 
sir plusSales ManagementCommented:
Got me
Does it ever recover even if you wait a long time?
It happens whenever your code is running in a loop or excel is doing an autosave or calculate both of which can take a while both on a big sheet &/or over a network

Try turn calculation to manual & disable autosave
0
 
ScriptAddictCommented:
Can you get any clues from the Task Manager about what is not responding?
0
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

 
dlmilleAuthor Commented:
i did check the task manager - nothing was not responding.  Seriously, I toggle to the VBE editor and try to select something with the mouse or use short-cut keys and I hear beeps.  If I step-into a macro from the worksheet, I can see the debugger running and I can hit F8 to see it navigate through code - but getting in there with the interface doesn't work.

Happens to me 1 or 2 times/week - happens on different machines/configurations/etc.

Dave
0
 
ScriptAddictCommented:
So weird,  I assume you mashed all the appropriate buttons?  (And I do mean mashed)  

I would try the basics, esc, break etc.  

Is it also possible that for whatever reason the keyboard isn't registering the release of a keystroke?  

I find that sometimes on my keyboard when weird things are happening if I Mash my ctrl, alt, shift, and windows function keys things go back to normal.  

-SA
0
 
dlmilleAuthor Commented:
Works just fine in other applications, INCLUDING the active Excel workbooks I'm working with.

Dave
0
 
dlmilleAuthor Commented:
The machines (most of them, and specifically the one having the last problem) are standard Office 2007 32 bit installs on 64 bit OS's.  I don't use anyone's add-ins, generally, aside from Solver.

on the last instance, anyway, I started excel and ran an UPGRADE macro I've been working on that opens a large 3rd party model and modifies it to my standards (probably 6+ workbooks are opened and consumes 3-400 mb when I check the task mgr).  There are no userforms utilized in the upgrade process.

I don't use MZTools/Smart Indenter (though I'm intrigued by a recommendation from rorya on MZ Tools).

I'll download the disable/enable into VBE and put it in an add-in so I can run it the next time this occurs.  Also, I'll download the monitor you suggested to test as well.

This thread I'll hold open to the next instance I have the problem (could take a couple weeks), so I can check all this out.

Thanks,

Dave
0
 
aikimarkCommented:
Can you click the Run | Reset menu?
What happens if you press Ctrl+L?
0
 
dlmilleAuthor Commented:
If I click anywhere in the window, no response - just a beep.  You can simulate it by hitting f2 on a workbook and type something in - simultaneously toggle to the debugger already open while you're still in the formula worksheet entry and you'll see the EXACT behavior and hear the same annoying beep.
0
 
aikimarkConnect With a Mentor Commented:
Is your CPU pegged at 100 percent?

What kind of VBA code is in the workbook?

It might be useful to add a Debug.Print statement at the very start of each VBA routine to help you identify if the problem is related to code.
0
 
dlmilleAuthor Commented:
>>Is your CPU pegged at 100 percent?
I would have to check, but really don't believe so

>>What kind of VBA code is in the workbook?
Excellently written VBA :)

>>It might be useful to add a Debug.Print statement at the very start of each VBA routine to help you identify if the problem is related to code.
No code is running when this happens.  To prove that, when this happened last, I was able to run a macro with the Step-Into approach (and no fancy event classes were loaded at the time - simple spreadsheet model with its simple code, and my upgrade code which was doggedly serial in nature).  However, this has happened to me in other instances with different circumstances.

When it happens again, I'll have to respond to all the questions that have been asked as we'll have it under the microscope.

Dave
0
 
dlmilleAuthor Commented:
I'm really surprised I couldn't find with google other instances of this.  Like when I report having submitted stuff on E-E and to find later it never submitted, lol - falls on deaf ears.
0
 
rspahitzConnect With a Mentor Commented:
Dave, the time I've seen problems similar to that is when there was a hidden pop-up box waiting for you to respond.  If you alt-tab around, do you see any dialog boxes? (Or maybe minimize all: Winkey-D, then expand Excel to see if a box appears.)
0
 
dlmilleAuthor Commented:
Thanks for the input.  Will run through all the traps mentioned at the next occassion...
0
 
broro183Connect With a Mentor Commented:
Yes, I'm a fan of MZ Tools too, I think it is very useful but you'll never know until you give it a go ;-)

hmmm...?
Your comments that you have also "experienced in different circumstances" & currently with "simple code" makes me wonder:
- is there a lot of code, ie how big are the .bas files when you export them?
- or, have you made a lot changes in the modules during development?

Here's a DDoE link which mentions strange errors can start occurring when modules reach ~64kb.
http://www.dailydoseofexcel.com/archives/2004/10/13/module-size-limits/
Rob Bovey has an addin that will create a summary report workbook which lists module sizes etc (nb: it doesn't always save the report properly when differing file extensions are used, but I'm going to ask him if that can be changed): http://www.appspro.com/Utilities/Documentor.htm

I'm leaning towards a popup as a reason, but perhaps running "Rob Bovey's CodeCleaner" (another addin ;-)) over your project will help. I use it regularly and have never experienced any problems... so it must be good ;-)

http://www.appspro.com/Utilities/CodeCleaner.htm
This link includes v5.0 as a COM addin & v4.4 as a normal excel addin. There are a few people on the web who recommend it, for example:

Chip Pearson states: "As you do extensive editing to a VBA project, lots of "garbage" gets left in the file because VBA doesn't properly clean up after itself.  Not only does this increase the file size, but it can lead to very strange behavior at run time.  CodeCleaner exports all your VBA code to text files, removes the modules from your project, and then imports the code from the text files.   This forces VBA to clean out all the garbage and start with a "clean" copy of the code.   I very strongly recommend this add-in." sourced from: http://www.cpearson.com/excel/xltools.htm

http://www.add-ins.com/macro-products-for-Microsoft-Excel/ways-to-repair-vba-code/how-to-fix-vba-code.htm

Good luck solving the mystery :-)

Rob
0
 
sir plusConnect With a Mentor Sales ManagementCommented:
I have had this before when there was a message box or form displaying in an extended desktop only the other monitor was turned off or not plugged in so I couldn't see it
Drove me nuts

Make sure there is no extended desktop running.....


Also
Add a debug print to each line of code
Or even better write each line of code to a log file as it executes so you find out if its your code looping

PS
Rob
CODE cleaner didnt help me but my spreadsheet is only 20 mb........
0
 
sir plusSales ManagementCommented:
Hey
It is th edebug window thats freezing & not excel
If it was excel it might just be that you were in debug mode.........
0
 
dlmilleAuthor Commented:
That's correct, the debug window doesn't accept key/mouse strokes as in editing VBA, using menus.  It is NOT frozen, as I explained, I can still step thru macros with F8 button.

This has not reocurred at this point, so cannot feedback as yet.  I might need a few weeks as it was intermittent - sometimes only a couple times a month.

Dave
0
 
dlmilleAuthor Commented:
Hooray!  I guess :)

It just happened...

I notice there's a BREAK in the debugger. F8 causes it to BEEP.  I MASHED the keyboard - and SOMETHING happened - now the BREAK is no longer there.  Help came up...

F2 caused the project explorer to come up...  The FUNCTION keys seem to work...   Keeping this going....

Hmmm.  Tried Alt-Q and it closed the debugger....

Well, brought the debugger back up.  Still BEEPING, lol.  Pulled up debugger with the different workbooks I have (I have a few open), and still BEEPS.

Nothing in the performance monitor that helps me.  Process monitor shows a bunch of excel workbooks only, all RUNNING.  Only one instance of Excel running.

As I was able to close the VBE explorer window, I could minimize other stuff and see no hidden popups.

CPU running at 17%.  There is freememory available.

F5 pulls up the RUN menu, but can't click anything on the GUI without getting the BEEP.  Ctrl-L does nothing.

Sorry - not going to put a debug print to each line of code - too much code for that.  However, the last code that was run had a log file and everything ended with success.

Still BEEPING, but hopefully answered questions you were asking.  Ready for more.  The app is still up, but I need to cycle it soon.

Dave
0
 
aikimarkCommented:
Alt+Tab and walk through the windows or launch the Task Manager to see if one of them might be showing a dialog window.
0
 
dlmilleAuthor Commented:
Did that.  No joy.
0
 
rspahitzConnect With a Mentor Commented:
>I notice there's a BREAK in the debugger. F8 causes it to BEEP.  I MASHED the keyboard - and SOMETHING happened - now the BREAK is no longer there.  Help came up...

I've seen cases where breakpoints sometimes get stored with the VBA even after closed and re-opened.  Maybe it's hitting that breakpoint and waiting for F5?
However, the way to get rid of it (which is unreliable) is to turn on the breakpoint, run it again, save it (but don't close it) then run it again.  When the breakpoint is reached, turn it off, save it and close it.  Hopefilly when you re-open it's gone...?

...or did I minunderstand that it's stopping on a "hidden" breakpoint?
0
 
broro183Commented:
hmmm, I didn't think the Process Monitor would help, but there was always a slight chance ;-)

In the VBE - [alt + t + o] and on the General tab:
- Is the error handling setting shown as "Break on all errors"?
- Is the "notify before state loss" option ticked or not?

I think (?) CodeCleaner would have the same effect as Rob S's suggestion, but I'm not sure as I have just tried exporting a module which includes an [F9] style break and I couldn't see any "hidden" attributes listed when opening the exported module in a text editor (notepad).

What size does the CodeDocumentor addin's generated report state for the specific module & the others in the project?
0
 
dlmilleAuthor Commented:
Will check - but need to see it break again.

I didn't try code cleaner or documentor.  Is that something I should do?

Dave
0
 
broro183Commented:
They can't hurt (too much!) & you'll never know unless you try them ;-)

CodeCleaner, yes, most definitely run it, whereas the Documentor was just a suggestion to easily identify the "size" of all your code modules (see the DDoE link in #37049453).
(Just like normal, I'd take a copy of your file first)

Rob
0
 
dlmilleAuthor Commented:
You know, this might be a very good idea.  Its a model with about 30 modules, some extensive.  I don't want it to move the code around (e.g., combine/split modules).  The part I'm currently working on is upgrading to the new model from old and to kick it off, it loads VBA modules that do all the rest of the upgrade.  Perhaps its that working with the VBE that might needs be cleaned as well.
0
 
dlmilleAuthor Commented:
What I got was a lot of expertise on this issue.  As the problem is sporadic, I'm going to have to keep examining each of the tests I was given and see if one "clicks".  I'm not sure I hit them all.  I'm closing this out as its not a lottery (just to see which one worked) but more of a collaborative effort, and all suggestions were good.

Cheers, fellow Experts.

Dave
0
 
broro183Commented:
Good luck Dave :-)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.