Which Window Is Active

Question

I have a short macro that places a cell in edit mode using the command:

Application.SendKeys "{F2}"

The line of code runs properly if the macro is run from the worksheet using, say, the normal Ribbon interface (that is because F2 puts the active cell into Edit Mode)

If I run the macro from the VBE window, the macro fails because the VBE interprets F2 to be a command to bring up the Object Browser.

I want to include a test in the macro to check for this:

If the worksheet is the active window, proceed running.
If the VBE is the active window, then issue a warning MsgBox and abort.

I don't know how to determine which window is active. ActiveWindow.Caption always displays something like:

Book1.xlsm

Answer 1

I think you ran into a VBE issue unfortunately. If the solution is to avoid making mistakes while coding, will this work for you? It doesn't appear that there is a way to see if the activewindow is the VBE that I could find. If this solution won't meet your needs, Using VBS in WHS might work for you or calling an external program like autoit.

Note that it won't work if you are stepping through the code but I assume that you are really mainly concerned about hitting the run button from within the VBE window and it will work for that. Put the first line at the top of your code or right before the section with sendkeys or whatnot.

ThisWorkbook.VBProject.VBE.MainWindow.Visible = False
If ActiveWindow.Caption <> "Book1.xlsm" Then MsgBox ("Test") Else MsgBox ("testelse")

You won't be able to alt-tab back, have to hit Alt-F11