6

I'm trying to automatically display the IDE when Excel is launched.

Is there a way to simulate a click on the "Visual Basic Editor" icon in the ribbon? I looked into Application.CommandBars but there's nothing about the Ribbon.

Private Sub Workbook_Open()
    ' Display Visual Basic Editor
End Sub
Community
  • 1
  • 1
michael
  • 929
  • 6
  • 19

2 Answers2

8

The Commandbars object has an ExecuteMso method that allows you to "push" any Ribbon button, so:

Application.CommandBars.ExecuteMso ("VisualBasic")

As noted by Comintern, the Application qualification is necessary when using this in a Workbook_Open event, otherwise you'll get an error 91.

To find the mso, go into the Quick Access toolbar's Ribbon menu, find what you want and hover:

enter image description here

Doug Glancy
  • 27,214
  • 6
  • 67
  • 115
  • +1 for not requiring programmatic access. Note that `CommandBars.ExecuteMso` needs to be qualified with `Application.`. – Comintern Aug 04 '16 at 01:19
  • Thanks @Comintern. I didn't have to qualify it, but I'm generally a verbose coder (when not racing the SO clock), so I'll add that. – Doug Glancy Aug 04 '16 at 01:20
  • 1
    Weird. It gives me an error 91 (Excel 2013 x32) when it isn't qualified. I wonder if the Workbook isn't ready in time for the call if it's in `Workbook_Open()`. – Comintern Aug 04 '16 at 01:23
  • @Comintern, you are oh-so-right about that. When I actually do the tedious work of stuffing it in Workbookj_Open, save it and re-open, it fails without the Application bit. Thanks! – Doug Glancy Aug 04 '16 at 01:27
  • 1
    @Doug Glancy, thanks, this is exactly what I was looking for. – michael Aug 04 '16 at 01:52
  • 1
    It's not a function so you can drop those parentheses. That then gives you `Application.CommandBars.ExecuteMso "VisualBasic"`. Right now they are just telling VBA to "evaluate this bit first", which you don't need. – Greedo Sep 08 '18 at 09:50
3

It would be

Private Sub Workbook_Open()
    ' Display Visual Basic Editor
    Application.VBE.MainWindow.Visible = True 
End Sub

If you get Programmatic Access error: Programmatic Access To Visual Basic Project Is Not Trusted - Excel

Community
  • 1
  • 1
Oscar Ortiz
  • 813
  • 8
  • 16