Structure the user form macro code

We’ve just seen how to view our user form macro code:

Now we’re going to have to think about what we might want each piece of code to do.

Code for the user form buttons

Structuring the user form macro code

We’ve added option buttons and a command button to the user form. We need to think about what the code behind each of those buttons is going to do. Here’s a suggestion:

  • Code for the command button – when the user proceeds and clicks on the command button we want the macro to run and output the results of our analysis. We’re going to have to insert some code within the command button code that makes this happen.
  • Code for the option buttons – when the user chooses one of the three option buttons we’re going to use that information to determine what results should be provided. We’re going to use the information generated when the user picks an option, but we don’t actually need anything else to happen or run when the user selects an option. That means we don’t need to place any code inside the option buttons for this example.

Sounding simple so far (the command button needs some code, the option buttons don’t)? Maybe – but there’s a bit more to it yet.

Code for the command button

The macro code for the command button

Here’s some code that you can use for the command button. If you read the green notes in the picture below you should be able to tell what each part of the code is doing. Clicking the command button outputs the results of the macro using simple message boxes.

The macro ‘knows’ which option has been selected and makes use of that information. The key piece of code here (for the first option button) is:


    If UserForm1.OptionButton1.Value = True Then
    MsgBox "The IRR of your investment is " & InternalRateOfReturn
    End If

Rather than type all the code in, you can copy and paste it from here:


    Private Sub CommandButton1_Click()
    'Clicking the proceed/ command button starts this routine running


    If UserForm1.OptionButton1.Value = True Then
    'Output IRR in a message box
    MsgBox "The IRR of your investment is " & InternalRateOfReturn
    End If


    If UserForm1.OptionButton2.Value = True Then
    'Output money multiple in a message box
    MsgBox "The money multiple for your investment is " & MoneyMultiple
    End If


    If UserForm1.OptionButton3.Value = True Then
    'Output NPV in a message box
    MsgBox "The NPV for your investment is " & NPV
    End If


    End Sub


    Private Sub OptionButton1_Click()
    'No routine started when the option button is selected. But Excel still remembers whether the button has been checked and that's a useful piece of information when we run the routine above
    End Sub


    Private Sub OptionButton2_Click()
    'Comment as above
    End Sub


    Private Sub OptionButton3_Click()
    'Comment as above
    End Sub


    Private Sub UserForm_Click()
    'Comment as above
    End Sub

Or you can download the latest version of the example here: custom user form macro v2. Remember, it’s not working yet (don’t worry about any errors you get) but this latest version brings you up to date with our work so far. If you download the example, double-clicking on one of the user form buttons will take you to the code.

Getting the user form working

Now that we’ve assigned some code to the buttons, we need to get the whole macro working. We’re going to concentrate on getting the user form working.

About this online Excel course material

You’re looking at a free online course extract covering different kinds of Excel VBA macro dialog boxes and user forms.

...join the conversation...

Your email address will not be published. Required fields are marked *