Making A Macro Interface

Hi Naxos,

If you want to create one or more buttons which when you press them activate a macro this is the way to do it.

View
Toolbars
Tick Forms
Click the Button icon
Move mouse arrow to spreadsheet
Crosshairs will appear
Left click and draw a rectangle with the mouse
Release Mouse button
Macro list will appear
Assign whichever macro you want to activate when you click the button
To edit the text on the macro button
Hold down control and left click on button
Select text and type in your own

Repeat to create a second button for a different macro.

Hope that helps and was what you were after.

Tim.

My preferred way to create buttons for macros in excel is to create a new commandbar. Your buttons will be incorporated directly in the Excel toolbar space. This works great.

Here is some code to help you if you decide to do it this way:

'Creates the command bar to hold macros
Sub CreateCommandBar()
    Dim cb As CommandBar
    Dim cc As CommandBarButton
    Dim NewButton As CommandBarButton


    Set cb = Application.CommandBars.Add(CommandBarName, msoBarTop, False, True)
    With cb
        
        Set NewButton = cb.Controls.Add(msoControlButton, , , , True)
        With NewButton
            .Caption = "Start New Analysis"
            .OnAction = "'" & ThisWorkbook.Name & "'!MACRONAME"
            .TooltipText = "DESCRIPTION OF WHAT MACRO DOES"
            .Style = msoButtonIconAndCaption
            .FaceId = 240
        End With




       Set cc = Nothing
        .Visible = True
        .Left = 0
        .Top = 100
    End With
    
    Set cb = Nothing
End Sub

Piece of cake. Lets say the Function to create the menu bar is called "CreateCommandBar"... just add the following SUB anywhere in your Modules, and it will auto run.

Sub Auto_Open()
    Call Utils.CreateCommandBar
End Sub

You may have to delete your menu at some point, to do so, here is the code:

Function DeleteCommandBar()
    On Error Resume Next
        For Each cb In Application.CommandBars
            cb.Delete
        Next cb
    On Error GoTo 0
End Function