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