Excel training videos

Optionbuttons and Userforms MS Excel

If you have to choose from many options and then perform calculations in your Excel worksheet, then you can use option buttons in your userforms. As we mentioned earlier, userforms provide a great interface to enter data. Because you have labels next to the controls where you enter data, data entry in userforms is quick and easy. In our example, employees have the option of receiving a house rent allowance or a renovation allowance, if they live in their own house. Selecting one of the optionbuttons triggers a calculation with the correct data once you hit the command button.

Complete Excel VBA code:

Private Sub CommandButton1_Click()
Dim sal As Integer
Dim hra, ra As Single

Dim eRow As Long
Dim ws As Worksheet
Set ws = Worksheets("data")

'find first empty row in database
eRow = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row

'check for a salary entry
If Trim(Me.TextBox1.Value) = "" Then
  MsgBox "Please enter a salary"
  Exit Sub
End If

'copy the data to the data sheet
sal = TextBox1.Text
ws.Cells(eRow, 1).Value = Me.TextBox1.Value
' calculate house rent allowance at 60%
If OptionButton1.Value = True Then
hra = sal * 0.6
TextBox2.Text = sal + hra
' calculate renovation allowance at 30%
ra = sal * 0.3
TextBox2.Text = sal + ra
End If

ws.Cells(eRow, 2).Value = Me.OptionButton1.Value
ws.Cells(eRow, 3).Value = Me.OptionButton2.Value
ws.Cells(eRow, 4).Value = Me.TextBox2.Value
'clear the data
Me.TextBox1.Value = ""
Me.OptionButton1.Value = ""
Me.OptionButton2.Value = ""
Me.TextBox2.Value = ""

End Sub

Private Sub CommandButton2_Click()
End ' You can also use Unload Me
End Sub

The training video gives the accurate details in action.

 Further Reading
Excel training videos