Now click on the first label you created, then double-click on the Caption property in the Properties window and type: Part Number. Click on the TextBox to the right, then in the Properties window, double-click on Name property and type: txt_PartNumber. Now you have to repeat the above step for the remaining textboxes, combo box and labels. You would add: 

     a textbox named txt_CurrentTSL, with a label Current TSL Qty
     a textbox named txt_NewTSL, with a label New TSL Qty
     a combo box named cbo_Reason, with a label Reason for change
     a textbox named txt_Date, with a label Date of change

If the text boxes are not aligned, you can align them by dragging on the handle.

ListBox and ComboBox Controls
 
ListBox displays a list of values from which you select one or more. If the ListBox is bound to a data source, then the ListBox stores the selected value in that data source. The ListBox can either appear as a list or as a group of OptionButton controls or CheckBox controls.

ComboBox Control combines the features of a ListBox and a TextBox, and allows the user to enter a new value, or the user can select an existing value as with a ListBox.

The default name for the UserForms you created is called UserForm1, UserForm2, etc. It is a good practice to give the userform and its controls descriptive names because if you have multiple userforms, the default names can become a bit of pain in telling the difference. To rename UserForm1, in the Properties window, double-click on Name property -UserForm1at the top right of the window, and type: frm_TSLrecord. The userform name change in the Project Explorer, but the form still shows UserForm1 in its title bar. To change it from title bar, in the Properties window, double-click on the Caption property - UserForm1, and type: TSL Change Record. The title bar will now display the new caption.

Adding Frame, OptionButton and CheckBox to the UserForm

Using the Frame tool, you can draw a Frame. Then you draw the Option buttons to contain inside the frame. The frame is used to group the option buttons together that separates them from the text box controls on the userform. OptionButton Control show you whether a single item in a group is selected. If an OptionButton is bound to a data source, the OptionButton can show the value of that data source as either Yes/No, True/False, or On/Off. Depending on the value of the TripleState property, an OptionButton or a CheckBox can also have a null value. You can also use an OptionButton inside a group box to select one or more of a group of related items.

It is worth to take note of the TripleState Property and functionality. TripleState determines whether a user can specify, from the user interface, the Null state for a CheckBox or ToggleButton. Although the TripleState property exists on the OptionButton, the property is disabled. When the TripleState property is True, a user can choose from the values of Null, True, and False. The null value is displayed as a shaded button. When TripleState is False, the user can choose either True or False. A control set to Null does not initiate the Click event.

Next using the CheckBox tool, you draw a check box on the form. You would use a CheckBox to give the user a choice between two values such as Yes/No, True/False, or On/Off. A disabled CheckBox is dimmed and does not allow changes to the value as you see in the diagram below that the Enabled property is set to False.

To allow users to perform an action, you have to add command buttons to the user form. In the Toolbox, click on the  CommandButton button to draw on the userform. Select the CommandButton, double-click on the Name property in the Properties window and type: cmdAdd. Double-click on the Caption property and type: Add this part. Repeat the above steps to add another two command buttons named cmdCancel with a label Cancel, and cmdClearForm with a label Clear Form.

     a frame named FramLevel, with a label “Request From”
     an option button named opt_Customer, with a label “Customer”
     a textbox named opt_Field, with a label “Field Engineer”
     a textbox named opt_Division, with a label “Product Division”
     a checkbox named chk_Immediate, with a label “Immediate effect”
     a textbox named chk_NonCritical, with a label “Non-critical”
     a textbox named cmdAdd, with a label “Add this part”
     a textbox named cmdClearForm, with a label “Clear Form”
     a textbox named cmdCancel, with a label “Cancel”

 

Writing Event code for the UserForm

Events occur when the user does something such as – click on a button, enter text in textbox, click an option button, right-click on a cell, etc. Each of these events has a built-in event handler, where the subs are always available so that you can write appropriate code to it. Of course, there are other event handlers you want to ignore such as where the mouse is dragged over a command button.

Of all the VBA code I have shown you thus far are codes that you placed in standard modules. Event code is different which you have to place in the userform’s code window. To get to it, select View | Code from the VBE menu, or right-click on the UserForm and select View Code. Another way to get from the design window to the userform’s code window is to double-click on a control. This not only open the code window, but it also insert a private sub for the event code. The footer, header, and default action for the form’s control is automatically inserted for you. For example, when you double-click the Cancel button in design view, the following sub is inserted in the code window.
Private Sub cmdCancel_Click() 

End Sub

 

 

If you select other control from the Object drop-down view on the left, you would be able to see a list of other events that are available in the drop-down Properties. For example, if you select UserForm from the Object drop-down list, you would be able to see the event QueryClose at the drop-down list on the right, which I will explain to you in the next example.

If you click on the event item QueryClose, you will get the following private sub automatically inserted for you. You can not change the format of the sub and it must be named as shown below (control name, followed by an underscore, and the event type), and it must contain any arguments that are given.
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer) 

Show, Load, Hide and Unload the UserForm

A userform can be called from any module. Below is the Initialize event procedure for the userform frm_TSLrecord, sitting in the module. The Load method allows the userform to be loaded, but remains hidden. The Show method pops up the form you have just created.
Sub OpenTSLrecord_Form()
   Load frm_TSLrecord
   frm_TSLrecord.Show
End Sub

Introducing basic form Controls

Below is an example of asking input from the user, using the same userform that I have created in the previous example. After the text boxes are filled, combo box, option and check buttons are selected, and the user clicks OK, the information will be appended to a sheet as shown in the diagram. There are five event handlers: UserForm_Initialize, cmdAdd_Click, cmdClearForm_Click, cmdCancel_Click and chk_Immediate_Change. These event procedures would be placed in the form you have named as frm_TSLrecord. You can DOWNLOAD this example here.

Private Sub cmdAdd_Click()
Dim lastrow As Long
lastrow = Worksheets("datasheet").Range("A65536").End(xlUp).Row
ActiveWorkbook.Sheets("datasheet").Activate
Cells(lastrow, 1).Select
Do
  If IsEmpty(ActiveCell) = False Then
     ActiveCell.Offset(1, 0).Select
  End If
Loop Until IsEmpty(ActiveCell) = True
With txt_PartNumber
  If .Value = "" Then
     MsgBox "you must enter the part number"
     .SetFocus
     Exit Sub
  End If
End With
With txt_CurrentTSL
  If .Value = "" Or Not IsNumeric(.Value) Then
     MsgBox "Current TSL must be numeric and must not be blank"
     .SetFocus
     Exit Sub
  End If
End With
With txt_NewTSL
  If .Value = "" Or Not IsNumeric(.Value) Then
     MsgBox "New TSL must be numeric and must not be blank"
     .SetFocus
     Exit Sub
  End If
End With
With cbo_Reason
  If .ListIndex = -1 Then   ‘ -1 means no item is selected. ListIndex = 0 means first item is selected
     MsgBox "you must chose a reason for change from the list box"
     .SetFocus
     Exit Sub
  End If
End With
With txt_Date
    If .Value = "" Or Not IsDate(.Value) Then
        MsgBox "Date Changed must not be blank and must be date format"
        Me.txt_Date.SetFocus
        Exit Sub
    End If
End With
  If opt_Customer = True Then
     ActiveCell.Offset(0, 5).Value = "Customer"
  ElseIf opt_Field = True Then
     ActiveCell.Offset(0, 5).Value = "Field Engineer"
  Else
     ActiveCell.Offset(0, 5).Value = "Product Division"
  End If
  If chk_Immediate = True Then
     ActiveCell.Offset(0, 6).Value = "Yes"
  Else
     ActiveCell.Offset(0, 6).Value = ""
  End If
  If chk_NonCritical = True Then
     ActiveCell.Offset(0, 7).Value = "Yes"
  Else
     If chk_NonCritical = False Then
        ActiveCell.Offset(0, 7).Value = ""
     Else
        ActiveCell.Offset(0, 7).Value = "No"
     End If
  End If
ActiveCell.Value = txt_PartNumber.Value
ActiveCell.Offset(0, 1).Value = txt_CurrentTSL.Value
ActiveCell.Offset(0, 2).Value = txt_NewTSL.Value
ActiveCell.Offset(0, 3).Value = cbo_Reason.Value
ActiveCell.Offset(0, 4).Value = txt_Date.Value
Unload Me   ' unload the form
End Sub

Private Sub cmdClearForm_Click()
    Call UserForm_Initialize
End Sub

Private Sub cmdCancel_Click()
    Unload Me   ' or frm_TSLrecord.Hide
    End
End Sub

Private Sub chk_Immediate_Change()
If chk_Immediate = True Then
   chk_NonCritical.Enabled = True
Else
   chk_NonCritical.Enabled = False
   chk_NonCritical = False
End If
End Sub

UserForm_Initialize event handler determines how the userform will look like when the user first opens it. The “Reason for change” combo box should be filled with a list of pre-defined reasons, and only the “Customer” Option button is checked. Value property is the default property for an option button and a check box, which is either True or False. You can simply leave out the word .Value from the line opt_Customer.Value = True. Similarly, to make a text box blank, you can write txt_PartNumber = " " 

When the user click the Clear Form button, it erases the content which is because the cmdClearForm_Click event code is calling UserForm_Initialize event handler. When the user click Cancel button, the form disappears, which is driven by Unload Me, and then it ends the program. 

cmdAdd_Click event code captures the user’s input. Usually, these user’s inputs are stored in public variables, declared in a standard module, so that later you can re-use them again in the main module. Of course, these variables are recognized by the event code. This code performs errors-checking whether the user: (1) leave the text box blank, (2) type in non-numeric string in a numeric-only textbox, (3) fail to select an item in a combo box, (4) enter a value that is not in date format, (5) not selecting a checkbox. In either case, the code will display an alert message. ListIndex property identifies the currently selected item in a ComboBox or ListBox. When the user selects a row in the combo box, the system sets the ListIndex value. It starts with 0, and -1 indicates that no item is selected. This explains the error check. Below diagram is the output in datasheet when the Add button is clicked.
 

 

Create a Button to open the UserForm

To make it easy for users to open the UserForm, you can add a button to a worksheet, you then assign the following macro you created in the module to this button.

Sub OpenTSLrecord_Form()
    frm_TSLrecord.Show
End Sub

How to add a button?
On the Drawing toolbar, click on the rounded rectangle tool.
In the centre of the worksheet, draw a rectangle, and format as you desired.
Select the rectangle and type: “Click here to ADD part information”.
Right-click on the rectangle border, and choose 'Assign Macro'.
Select the macro name UserForm_Initialize, click OK.

 

Working with ListBox

List boxes are a lot trickier than combo box. There are four methods that I know you can populate them. If the ListBox items are not contained in a worksheet range, you can use AddItem method to fill the ListBox as shown in example 1.

‘ write in your module
Private Sub Example1_ListBox1()
With frm_Customers.ListBox1
    .RowSource = ""   ‘ to avoid getting “permission denied” error if there is nonempty RowSource setting
    .AddItem "SSMC"
    .AddItem "UMC"
    .AddItem "TSMC"
    .AddItem "IMF"
    .AddItem "INTEL"
End With
frm_Customers.Show
End Sub

You can also use AddItem method to fill in ListBox items from a range or a named range as shown in example 2. You can hide Sheet2 in case the content may accidentally be deleted by user.
Private Sub Example2_ListBox1()
With frm_Customers.ListBox1
Dim r As Integer
For r = 1 To 5
  frm_Customers.ListBox1.AddItem Sheets("Sheet2").Cells(r, 1)
Next
End With
frm_Customers.Show
End Sub

If you have named a range in Sheet2.Range(“A1:A5”) as "MyCustomers", you can substitute with a dynamic range as shown below.
Private Sub Example3_ListBox1()
Dim cell As Range
With frm_Customers.ListBox1
For Each cell In Sheets("Sheet2").Range("MyCustomers")
  frm_Customers.ListBox1.AddItem cell.Value
Next
End With

In the Property Window, if the RowSource property of the form frm_Customers is set to the named range "MyCustomers" at design time, the use of For..Each Next loop can be omitted. This setting tells VBA to populate the ListBox with the items in “MyCustomers” range. The diagram below is what the ListBox should look like using any of the above 4 methods.

 

This CheckBox event code allows you to check and uncheck the option button you have selected in ListBox 1. In the whole exercise of this example , it checks on valid data entry on each textbox and listbox. The Add Items command button would add the user input into Sheet2 which is hidden. The diagram below is the userform I have created. You can DOWNLOAD the example here.
Private Sub CheckBox1_Click()
Dim i As Long
For i = 0 To ListBox1.ListCount - 1
    ListBox1.Selected(i) = CheckBox1
Next
End Sub

 

Single and Multi Selection ListBox

The procedure below take a single selected ListBox item and places it into a range in Sheet2
Sheet2.Range("A65536").End(xlUp)(2, 1) = ListBox1.Value

For such code to work the ListBox must have its MultiSelect Property set to 1 fmMultiSelectSingle. When you set the MultiSelect Property set to -1 fmMultiSelectMulti, you can allow user to make multiple selections. However, due to MultiSelect Property set to -1 fmMultiSelectMulti we can no longer use simple code like above to return the selected items to our cells. To do this with multiple selections we can use code like shown below;

Private Sub CommandButton2_Click()
Dim lItem As Long
    For lItem = 0 To ListBox1.ListCount - 1
        If ListBox1.Selected(lItem) = True Then
            Sheet2.Range("A65536").End(xlUp)(2, 1) = ListBox1.List(lItem)
            ListBox1.Selected(lItem) = False
        End If
    Next
End Sub

Below is an example of the listbox using MultiSelect Property set to -1 fmMultiSelectMulti. DOWNLOAD the example here.