How to Switch from Worksheet to VBE Screen

[Cyberdude]

I have a worksheet that has in column ?C? a list of all the macro names in the workbook. My objective is to be able to double click on a macro name, and it will take me to the VBE, which is now displaying the code of the macro whose name I double clicked on. Ideally it will work exactly as it does when you click on TOOLS->MACRO->MACROS, then click on the macro name that you want to go to in the VBE. To implement this, I created a ?WorkSheet_BeforeDoubleClick? event macro which does a little analysis, then calls the macro ?GoToMacroName?.

The macro ?GoToMacroName? contains one line:

Application.Goto Reference:=Selection.Value

This works like a charm, except the logic doesn?t switch me to the VBE window. When I manually open the VBE, then, voila!, the selected macro is there, just like I want it to be. So my question is, what VBA command do I write to switch me from my worksheet screen to the VBE screen?

[Zack Barresse]

Hey,

I bet you could use the code by Chip Pearson here: Programming to the VBE.



HTH

[Cyberdude]

Maybe I missed it, but I didn't see what I'm looking for. His stuff all deals with the condition that all action occurs within the VBE. My macro needs to switch me from the worksheet to the VBE, in much the same way you would switch between worksheets or workbooks. Except VBE isn't a workbook or a worksheet. I'm not sure how to "activate" it. (Sigh)

[Scottie P]

Cyberdude, Dude.


Is this what you are looking for:

'// This code will take you to the Sub (in the VBE) defined in the .Goto line
'// in this case: .Goto "test1" (change to your sub name within " ")

Sub test1()
With Application
.Goto "test1"
End With
End Sub

Or a general sort of:

'// This code will generically open the VBE
Sub test2()
Application.VBE.MainWindow.Visible = True
End Sub

Note that the second sub will also work in MS Word - not too sure about the first.



EDIT:: On a second tour of the Kb this eve, I found this:
http://www.vbaexpress.com/kb/getarticle.php?kb_id=331

It is a write up on the above codes - forgot I put this one in there!

[Cyberdude]

Scott, your suggestion to use

Application.VBE.MainWindow.Visible = True

worked great for me. However, then I had another problem. When I executed the code, I could see the VBE window, but everything froze. After fooling around I discovered that I had to select another cell on the worksheet before it would unlock. That is clumsy, but it works just fine for me. Thanx for the great suggestion.

[Scottie P]

That's odd - I've never had that occur (not that I don't believe it though).
I will take a look at it some more - perhaps it's just a focus issue... "?"

Scott

[Paleo]

Great solution Scott, thanks I will use it too!

[Scottie P]

I'm Glad folks are finding it useful; I use it all of the time.
I forget when I came across this (over a year ago for sure and most likely in the Newsgroups somewhere). When I send someone a sample, I have a button on the first sheet so that the user can go straight to the VBE to see the code, make changes where they are needed, etc. It is a handy tool in diagnostics too.

What is odd is trying to set WindowState (= 1, 2 or 3) when using this seems to make no difference, so this is not the answer to the latest issue with this particular code. I don't think that I am going to be able to further solve CyberDude's issue with the application window freezing - anyone have an idea on this?

Seems I haven't found the 'solve' for separating the App window from the VBE window in my code tests here this evening AND I cannot produce that 'freezing' result on my machine either.


Stumped.

Scott

[Cyberdude]

Scott, I wasn't asking for help in solving the "freeze" issue, so don't spend a lot of time on it. However, for your dining and dancing pleasure, I will show my basic code below:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)  '4/12/05
'If a cell in column "C" was double clicked, then process it
If Left(ActiveCell.Address(columnabsolute:=False), 1) = "C" _
   Then Call GoToMacroName
End Sub

Sub GoToMacroName()               '4/12/05   [Called by "Worksheet_BeforeDoubleClick"]
'This macro runs when you double-click on one of the names
'in the macro name list created by macro "ListMacroNames"
Dim MacNm$
MacNm = Selection.Value
ActiveCell.Offset(1, 0).Select
With Application
   .Goto MacNm
   .VBE.MainWindow.Visible = True
End With
End Sub   'GoToMacroName'

[Scottie P]

Hey Dude.

My interest in the freeze up was general concern (taking your "Clumsy, but it works" response as an indication that this would do for now). The code that I posted is a Kb entry as well. If someone else uses it and ends up with the same result (the freeze up) it could be a problem, ya know? So, I thought that now was a good time to look into this further. Outta site, outta mind otherwise...

I did check a bit last night and found that other folks have had issues when using the " Application.VBE.MainWindow.Visible " in conjunction with their code - in those cases it was an issue of returning focus to the App window, but each instance is different (of course) and appears to be directly related to what was occuring at the time the VBE opened, and not a general failure to return focus. Attempting to use "WindowState = 2" (or 3) really didn't seem to help them out.

In any event, something like this is good to know about and (as I have time) I will continue looking for an alternative/fix as I can.

Thanks for reposting here and thank you for posting your code as well; this may help me reproduce the problem and thus allow to me to get on with sorting it out (glad to know it is not a critical issue for you at the moment )

Scott