Design User Form with ListBox Labels TextBoxes OptionButtons Command Button

I have a question about a project I am trying to do. I don’t know much about lines of code, but do you think it is hard to design a calculator that can do something like this:
Ex1: Say I only have red, yellows, and blue M&Ms and the questions were asked like this: How many M&Ms do you want? 12 M&Ms
How many M&Ms do you want to be red? 0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12 I select 6
How many blue M&Ms do you want? 0, 1, 2, 3, 4, 5, 6 I select 3 Automatically there is a calculator running behind the scenes and it adds 3 yellow M&Ms now. It does this because the calculator already *knows* I want 12 M&Ms, I only have 6 red and 3 blue, and automatically completes the cart.
Also you can tell that it removed 7 – 12 because clearly I can’t select those if I only want 12 M&Ms.
Ex2: Select the packaging you want your M&Ms to be in: Standard, Box, or Wrapping If I select “Box” it automatically will add a $5 charge to a list that will eventually add everything up and produce a total. So even though it says “Box” as the click-able button, the simple calculator knows Box = $5.
Is it difficult to make something like that? I appreciate any tips you can give me. Thank you for your time.

Click on the developer tab
Selct Visual Basic
In the Visual Basic Editor Window click on insert and select ‘user-form’
From the toolbox select the appropriate controls like label, list-box, textbox, option buttons, command buttons, etc one by one and then place them on the user-form by clicking and dragging
Keep the names as they are. Of course, you can give your own names if you like
Select appropriate captions for the labels and option boxes to make the form user friendly
You can also add ‘tool tips’ to the text boxes so that the user knows what he has to do
Once the controls have been added you need to code the command button to perform certain actions and the form to populate the items

The code for both the objects is given below:
Private Sub CommandButton1_Click()
TextBox3 = 12 – (Val(TextBox1) + Val(TextBox2))
If OptionButton1.Value = True Then TextBox4 = Val(TextBox1) * 1.5 + Val(TextBox2) * 1.7 + Val(TextBox3) * 1.8 + 5
TextBox4 = Val(TextBox1) * 1.5 + Val(TextBox2) * 1.7 + Val(TextBox3) * 1.8 + 10
End If
Range(“A3”) = ListBox1.Value
Range(“B3”) = TextBox1.Text
Range(“C3”) = TextBox2.Text
Range(“D3”) = TextBox3.Text
If OptionButton1.Value = True Then
Range(“E3”) = OptionButton1.Value
Range(“E3”) = OptionButton2.Value
End If
Range(“F3”) = TextBox4.Text
End Sub

Private Sub UserForm_Activate()
ListBox1.List = Array(“A”, “B”, “C”)
End Sub

You can change the italicized code to the following code to ‘box’ or ‘wrapping’ instead of true or false.

If OptionButton1.Value = True Then
Range(“E3”) = OptionButton1.Caption
Range(“E3”) = OptionButton2.Caption
End If

Remark: If you wish to use the above code as is, ensure that the names of your controls are also the same.

Watch the excel training video below to see how the solution is implemented. We have tried to keep the coding as simple as possible so that the user has little problems understanding the process:

Watch the video on YouTube


Mastering Excel: User Forms

2 thoughts on “Design User Form with ListBox Labels TextBoxes OptionButtons Command Button

  1. Richard

    sir i wanted to create a sunday school,i need your help can you please send me your mail id in person so that i good send me the designed excel file i want vba codings to finish it ..could you please help me please sir..

  2. rani dan suthar

    Sir I want coding for update button but an error show when click update button.
    I have coding like this. please help me.

    Dim B As String, C As String, D As String, E As String, F As String, G As String, H As String, I As String, J As String, K As String, L As String, M As String, N As String, O As String, P As String
    B = TextBox1.Text
    Cells(currentrow, 2).Value = B
    C = TextBox2.Text
    Cells(currentrow, 3).Value = C
    D = TextBox3.Text
    Cells(currentrow, 4).Value = D
    E = ComboBox1.Text
    Cells(currentrow, 5).Value = E
    F = DTPicker1.Value
    Cells(currentrow, 6).Value = F
    G = ComboBox2.Text
    Cells(currentrow, 7).Value = G
    H = ComboBox3.Text
    Cells(currentrow, 8).Value = H
    I = ComboBox4.Text
    Cells(currentrow, 9).Value = I
    J = ComboBox5.Text
    Cells(currentrow, 10).Value = J
    K = ComboBox6.Text
    Cells(currentrow, 11).Value = K
    L = ComboBox7.Text
    Cells(currentrow, 12).Value = L
    M = ComboBox8.Text
    Cells(currentrow, 13).Value = M
    N = TextBox13.Text
    Cells(currentrow, 14).Value = N
    O = TextBox14.Text
    Cells(currentrow, 15).Value = O
    P = TextBox15.Text
    Cells(currentrow, 16).Value = P


Leave a Reply

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