Tuesday, January 17, 2012

Generating a list of shortcuts unique to your version of MS Project [VBA]

I often get tired of searching Google for obscure shortcuts in Project (or Excel for that matter).  Solution?  VBA to the rescue.

Drop the code below in a module and setup a command to the macro List_Shortcut_Keys.

The code is 2 procedures:
  1. List_Shortcut_Keys: Opens a new Excel workbook, then initializes the actual meat of the code.
  2. ListCtrls: This is a recursive procedure that lists all the shortcuts from Project's Command Bar.  The code scans each toolbar in the commandbar and produces a list of shortcuts specific to the version of MSP you're running.   
If you have version/country specific shortcuts this will generate a list of shortcuts that will work for you.

I originally found the code at Chip Pearson's site and modified it for use in MS Project.  Given how MSP is comparatively undocumented the world of VBA, I figured this would help others who struggle finding resources for Project.

UPDATE: The code works with MSP 2003, 2007, and 2010.  Before running make sure you've selected Excel from the References dialog box.

Option Explicit
'Code modified by yunolikeexcel.com, Business Bear
'Original code found at http://www.cpearson.com/excel/ShortCuts.aspx

Sub List_Shortcut_Keys()
    Dim r As Range
    Dim xlApp As Excel.Application
    Dim wb As Workbook
    Dim ws As Worksheet

    Set xlApp = CreateObject("Excel.Application")
    Set wb = xlApp.Workbooks.Add
    Set ws = wb.ActiveSheet
    Set r = ws.Range("A3")
    ListCtrls Application.CommandBars.ActiveMenuBar, r, wb

    MsgBox "Process Complete.", vbSystemModal + vbOKOnly
    xlApp.WindowState = xlMaximized
    wb.Activate
    xlApp.Visible = True
    
    Set ws = Nothing
    Set wb = Nothing
    Set r = Nothing
    Set xlApp = Nothing

End Sub

Private Sub ListCtrls(ByRef Ctrl As Object, ByRef Rng As Range, ByRef wb As Workbook)

    Dim c As Office.CommandBarControl
    Static S As String
    Dim Pos As Integer

    If TypeOf Ctrl Is CommandBar Then
        S = "ALT"
    End If

    If Not TypeOf Ctrl Is Office.CommandBarButton Then
        For Each c In Ctrl.Controls
            Rng.Value = c.Caption
            Pos = InStr(1, c.Caption, "&")
            If Pos Then
                S = S & " " & Mid(c.Caption, Pos   1, 1)
                Rng.EntireRow.Cells(1, "H").Value = UCase(S)
            End If
            Set Rng = Rng(2, 2)
            ListCtrls c, Rng, wb
            Set Rng = Rng(1, 0)
            If Len(S) > 3 Then
                S = Left(S, Len(S) - 2)
            End If
        Next c
    End If

End Sub

No comments:

Post a Comment