CODEWITHSUNDEEP
vbavbe
Dschuli
Dschuli

Reputation: 379

Is there a way to define a shortcut key (e.g. Ctrl-Y) to start a VBA sub/macro while the VBE window is active?

I'm trying to automate some development steps by using VBA code and want to start those macros with a shortcut key while the VBE window is active/on top. Is there a way to do this? The Macro dialog in VBE does not offer the Options button, which allows to define a shortcut key in Excel proper. I also have not found a way to solve my problem via the VBE toolbar customization.

Upvotes: 0

Views: 1517

Answers (4)

Smizles
Smizles

Reputation: 29

TL;DR - You need to change the tools name and add an & in front of the letter you want to be the hotkey. The shortcut will now be Alt + <first letter following &>.

The above method is the only custom shortcut (a.k.a. Accelerator Key) method that is native to the VBE (if I'm not mistaken). I know this question is old, but it ranks much higher than the actual answer, so I thought I'd leave my results for the next person (which will probably be me again next year).

If you customize the VBE toolbar, you should be able to find a button labeled "Modify Selection" (you might need to click the "Rearrange Commands" button first from the Commands tab for a pop-up window with a working "Modify Selection" button if the button on the Commands tab is grayed out). On the drop-down context menu you'll see a textbox for the tool's name. This is the textbox where you add the & in front of the letter in the name that you want to use as the shortcut key. For effectively the same answer but slightly different procedure/implementation, see this answer: https://stackoverflow.com/a/23954017

Upvotes: 0

Dschuli
Dschuli

Reputation: 379

Thanks for the replies on my question. I was aware of the options presented there. As it seems that no real shortcut capabilities are available in VBE, I finally used a custom menu to access the VBA code I want to start during VBE sessions. I now have access to all of them (distributed over several modules and partially with long, description like names) via one macro (I call DM = short for DeveloperMenu) I (still) need to start via the immediate window.

Code below. Of course the specific menu items are custom for my environment and need to be adapted.

Sub Dm(Optional dummy As Boolean)
    Call CreateAndDisplayDevelopmentPopUpMenu
End Sub

Sub CreateAndDisplayDevelopmentPopUpMenu(Optional dummy As Boolean)
    Dim menuName As String

    menuName = "Development"

    'Delete PopUp menu if it exist
    On Error Resume Next
    Application.CommandBars(menuName).Delete
    On Error GoTo 0

    'Create the PopUpmenu
    Call DevelopmentPopUpMenu(menuName)

    'Show the PopUp menu
    On Error Resume Next
    Application.CommandBars(menuName).ShowPopup
    On Error GoTo 0
End Sub


Sub DevelopmentPopUpMenu(menuName As String)
    Dim MenuItem As CommandBarPopup
    'Add PopUp menu
    With Application.CommandBars.Add(name:=menuName, position:=msoBarPopup, _
                                     MenuBar:=False, Temporary:=True)

        'First add buttons

        With .Controls.Add(type:=msoControlButton)
            .caption = "&RebuildDefs"
'            .faceId = 71
            .OnAction = "'" & ThisWorkbook.name & "'!" & "'RebuildAllDefs ""True""'"
        End With

        With .Controls.Add(type:=msoControlButton)
            .caption = "&ToggleAddIn"
'            .faceId = 71
            .OnAction = "'" & ThisWorkbook.name & "'!" & "ToggleAddIn"
        End With

        'Second menues
        Set MenuItem = .Controls.Add(type:=msoControlPopup)
        With MenuItem
            .caption = "&Watch"

            With .Controls.Add(type:=msoControlButton)
                .caption = "&Start watch"
'                .faceId = 71
                .OnAction = "'" & ThisWorkbook.name & "'!" & "startWatch"
            End With

            With .Controls.Add(type:=msoControlButton)
                .caption = "&End watch"
'                .faceId = 72
                .OnAction = "'" & ThisWorkbook.name & "'!" & "DeleteAllwatches"
            End With
        End With

        Set MenuItem = .Controls.Add(type:=msoControlPopup)
        With MenuItem
            .caption = "&HeartBeat"

            With .Controls.Add(type:=msoControlButton)
                .caption = "&Start HeartBeat"
'                .faceId = 71
                .OnAction = "'" & ThisWorkbook.name & "'!" & "heartBeat"
            End With

            With .Controls.Add(type:=msoControlButton)
                .caption = "St&op HeartBeat"
'                .faceId = 72
                .OnAction = "'" & ThisWorkbook.name & "'!" & "stopHeartBeat"
            End With
        End With

    End With
End Sub

Upvotes: 1

FunThomas
FunThomas

Reputation: 29584

You cannot define Keyboard shortcuts within the VBE.
As long as your Subroutine gets no parameter:

  • put the cursor inside the subroutine and press F5
  • put the cursor outside of any routine and press F5 - you will get a list of Subs that you can start
  • Enter the name of the sub in the immediate window (Ctrl+G) and press Enter

If your Subroutine expects some parameter (even if it is only optional), only the method with the immediate window works. But you can write a short wrapper routine that calls the subroutine you want to call.

Upvotes: 0

Josh B
Josh B

Reputation: 1

The F5 key us the shortcut to run this. This can be viewed by hovering the pointer over the "continue" option in the toolbar so the dialog pop up displays.

Upvotes: 0

Related Questions