[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

VBE Editor doesn't respond to mouse/keystrokes

Posted on 2011-10-26
28
Medium Priority
?
475 Views
Last Modified: 2012-05-12
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
0
Comment
Question by:dlmille
  • 13
  • 5
  • 3
  • +3
28 Comments
 
LVL 5

Expert Comment

by:sir plus
ID: 37037359
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
 
LVL 11

Expert Comment

by:ScriptAddict
ID: 37041596
Can you get any clues from the Task Manager about what is not responding?
0
 
LVL 42

Author Comment

by:dlmille
ID: 37041631
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 11

Expert Comment

by:ScriptAddict
ID: 37041686
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
 
LVL 42

Author Comment

by:dlmille
ID: 37041939
Works just fine in other applications, INCLUDING the active Excel workbooks I'm working with.

Dave
0
 
LVL 10

Accepted Solution

by:
broro183 earned 900 total points
ID: 37042214
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
 
LVL 42

Author Comment

by:dlmille
ID: 37042274
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
 
LVL 46

Expert Comment

by:aikimark
ID: 37042542
Can you click the Run | Reset menu?
What happens if you press Ctrl+L?
0
 
LVL 42

Author Comment

by:dlmille
ID: 37042943
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
 
LVL 46

Assisted Solution

by:aikimark
aikimark earned 400 total points
ID: 37043081
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
 
LVL 42

Author Comment

by:dlmille
ID: 37043127
>>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
 
LVL 42

Author Comment

by:dlmille
ID: 37043130
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
 
LVL 22

Assisted Solution

by:rspahitz
rspahitz earned 400 total points
ID: 37046404
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
 
LVL 42

Author Comment

by:dlmille
ID: 37047083
Thanks for the input.  Will run through all the traps mentioned at the next occassion...
0
 
LVL 10

Assisted Solution

by:broro183
broro183 earned 900 total points
ID: 37049453
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
 
LVL 5

Assisted Solution

by:sir plus
sir plus earned 300 total points
ID: 37056087
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
 
LVL 5

Expert Comment

by:sir plus
ID: 37097298
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
 
LVL 42

Author Comment

by:dlmille
ID: 37097642
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
 
LVL 42

Author Comment

by:dlmille
ID: 37135696
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
 
LVL 46

Expert Comment

by:aikimark
ID: 37135739
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
 
LVL 42

Author Comment

by:dlmille
ID: 37135851
Did that.  No joy.
0
 
LVL 22

Assisted Solution

by:rspahitz
rspahitz earned 400 total points
ID: 37136108
>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
 
LVL 10

Expert Comment

by:broro183
ID: 37136649
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
 
LVL 42

Author Comment

by:dlmille
ID: 37161124
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
 
LVL 10

Expert Comment

by:broro183
ID: 37161291
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
 
LVL 42

Author Comment

by:dlmille
ID: 37161614
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
 
LVL 42

Author Closing Comment

by:dlmille
ID: 37196717
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
 
LVL 10

Expert Comment

by:broro183
ID: 37204493
Good luck Dave :-)
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question