Design Simple User-Form with ListBox, Labels, TextBoxes, OptionButtons & Command Button

Home More Videos


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 clickable 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.
  1. Click on the developer tab
  2. Selct Visual Basic
  3. In the Visual Basic Editor Window click on insert and select 'user-form'
  4. From the toolbox select the appropriate controls like label, listbox, textbox, optionbuttons, command buttons, etc one by one and then place them on the user-form by clicking and dragging
  5. Keep the names as they are. Of course, you can give your own names if you like
  6. Select appropriate captions for the labels and option boxes to make the form user friendly
  7. You can also add 'tool tips' to the text boxes so that the user knows what he has to do
  8. 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
Else
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
Else
Range("E3") = OptionButton2.Value
End If
Range("F3") = TextBox4.Text
End Sub

Private Sub UserForm_Activate()
ListBox1.List = Array("A", "B", "C")
ListBox1.SetFocus
End Sub

You can change the italicised code to the following code to 'box' or 'wrapping' instead of true or false.

If OptionButton1.Value = True Then
Range("E3") = OptionButton1.Caption
Else
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