CODEWITHSUNDEEP
excelvbaeditor
Larcondos
Larcondos

Reputation: 35

Excel Macro to open up the Visual Basic Editor

After completing a macro that opens when an Excel Workbook is opened (I'm sort of using a workbook as a makeshift executable in this sense), I then have a Messagebox appear and ask the user if they need to modify/look at the code, or if they are finished. If they're finished, it simply closes, but if they would like to look at the code they must then open up the Visual Basic Editor manually. This is fine, but it would be nice and smooth if it would simply pop up on it's own if they choose that option. Is there any function that opens the Editor window?

Upvotes: 1

Views: 2324

Answers (3)

JohnM
JohnM

Reputation: 3350

An alternative that doesn't require the user to reduce their security settings (I agree completely with the comment by pgSystemTester) and works for any UI language the user is using (the solution by pgSystemTester will not work for all language UI settings eg in Spanish the keys would be "%gv") is to use the relevant CommandBarButton for the VBE.

Sub OpenTheVbe()
    Dim isEnabled As Boolean
    ' where 21 is the "Visual Basic" CommandBar and 4 is the "Visual Basic Editor" CommandBarButton
    With Application.CommandBars(21).Controls(4)
        isEnabled = .Enabled
        .Enabled = True
        .Execute
        .Enabled = isEnabled
    End With
End Sub

This solution then doesn't have the usual problems with the use of SendKeys and works for any UI language that the user is using.

Upvotes: 2

pgSystemTester
pgSystemTester

Reputation: 9932

The Accepted Answer requires the user to reduce their security settings. I would strongly question a decision to reduce the overall security of the operating system simply so that a user can create a convenience macro that's essentially a shortcut. It's also worth noting that such a macro/shortcu already exists in the Excel Application -- the icon exists on the developer tab, but one could find it and place that same icon in the Quick Access Tool Bar or typing alt L V

If one still insists on making their own macro, see my answer here proposed on a duplicate question,

Application.SendKeys ("%lv")

Even though I'm personally not a fan of computer keystroke macros, in this case it makes sense considering the macro already was built by Microsoft...

Note that you could try ALT+F11 ("%{F11}) but make sure the keyboard has the function buttons (F1-F9 or higher) being activated on being pressed -- NOT a modifier (i.e. the FUNCTION key itself, which is probably confusing). Stated differently, you can open the VBE editor with either ALT+F11 or FUNCTION_BUTTON+ALT+F11.

Upvotes: 1

Алексей Р
Алексей Р

Reputation: 7627

Try this code:

Sub ShowVBE()
    ThisWorkbook.VBProject.VBE.MainWindow.Visible = True
End Sub

Upvotes: 1

Related Questions