Making A Macro Interface

  • How can I make the template with my macro have a worksheet that has Buttons, one would initilize a certain macro, another few might be graphing options that would activate other subs. I did a quick google search and didnt find much of a tutorial or anything really.

    Thanks :)

  • 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.

    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.


  • 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:

  • I like this a lot! I have a questions though.

    How can i get the customcommand macro to execute when the template is opened, so that the buttons will be there for everyone, even if they are not familiar with macros

    Thanks again!

  • 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
            Next cb
        On Error GoTo 0
    End Function
  • ok, one last quick, question about macro interfaces, with the command bar? say i want to add a new buttont to the right of the existing button in the code. How can i do that? so far i only get 2 buttons that are one on top of the other.


Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!