Working with Userforms

We have seen all the theory about Userforms (and some practice). In this section we build a Userfrom with all the components and macros associated with them from start to end. First, we design the form and add controls. Then we add events and actions to each of the controls. And finally, we add macros that perform those actions to a standard module.

 

Userform Design

The first step is to design the Userofrm and add the controls. We format the form template and add the controls by dragging them from the tool box (see more about Userform template here and Userform controls here). As an example, we have a project reporting form that allows to assign a RAG status to a given project (specified with a project ID), add it to a list, and then save or write that list to the worksheet.

 

The table below shows all the components/controls in the Userform. The name and caption of the Userform and its controls has been changed in the properties window (at design-time). The table also shows the description (or purpose) of the component/control (we will see more about that later).

Component	Name	Caption	Description/purpose
UserForm	projectForm	Project Reporting	Project reporting form
Label	Label1	Project ID	Descriptive text for tbProject
	Label2	RAG status	Descriptive text for cbRAG box
	Label3	List of projects	Descriptive text for lbProjects box
TextBox	tbProject	-	Accepts a project ID
ComboBox	cbRAG	-	Contains project RAG statuses drop-down
ListBox	lbProjects	-	List of added projects and status
Button	btnAdd	Add to list	Adds project and RAG to the list box
	btnSave	Save to sheet	Writes the list of projects to the worksheet
	btnClose	CLOSE	Closes the form

The labels describe the contents in the other controls (the text, combo and list boxes). The name of the labels has not been changed – they are by default Label1, Label2, and Label3. It is not necessary to change it. It could be useful though in case we want to program the labels (change any of the properties at run-time).

We set other necessary properties for the Userform or controls in the properties window. For example, we can set the MaxLength of the text box (tbProject) to 4 because the project ID is a 4 digit number (just as an example). Similarly, we may want to format the font or color of the text or any other property of this or other control.

 

Userform/Controls Events

We add a Userform_Initialize event procedure to set or apply any properties at run-time, just before the Userform opens. For example, we can populate the combo box with values here. As we will see later, that could also be done from within the calling macro that opens the form.

 Private Sub UserForm_Initialize()
     Dim RAGflag As Variant
     RAGflag = Array("Green", "Amber", "Red")
     cbRAG.List = RAGflag
 End Sub

We add an event procedure to restrict the input in the text box to only numbers (the project ID must be a number). We achieve that by locking the input if the ascii code of the key pressed is not between 48 and 57, the ascii codes for numbers. See the full list of ascii codes in this other page. Furthermore, and as explained earlier, the MaxLength property was set to accept only four digits.

 Private Sub tbProject_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
     If KeyAscii < 48 Or KeyAscii > 57 Then 'ascii for numbers from 0 to 9
         tbProject.Locked = True 'lock if NAN
     Else
         tbProject.Locked = False
     End If
 End Sub

Each button performs an action as per the code in their respective Click event procedures. The first button (btnAdd) calls the AddProject macro, which gets the project ID (tbProject) and RAG status (cbRAG) and adds it to the list of projects box (lbProjects). We will see that macro later.

 Private Sub btnAdd_Click()
     Call AddProject
 End Sub

 

The second button (btnSave) calls the macro SaveProjects, which loops through all the projects added to the list box (lbProjects) and writes them to the worksheet (see later).

 Private Sub btnSave_Click()
     Call SaveProjects
 End Sub

The third button (btnClose) closes the form. We could add some checks here if needed, for example to check if the data has been saved before closing the form.

Private Sub btnClose_Click()
Unload Me
End Sub

We are also going to use the Change event of the combo box to check if the project is amber or red to display a warning/reminder message about the process to follow (for example). As with previous event procedures, a macro is called (ShowAlert) to perform that task in a standard module.

 Private Sub cbRAG_Change()
     Call ShowAlert
 End Sub

 

Action Macros

Most actions or tasks derived from control events are included in one or more standard modules to keep the code organized and more accessible.  For this simple example, one module is enough; unless we still feel like further separating the code in various modules.

 

The first action is to open the Userform. This might happen when selecting data in the worksheet, pressing a button added to the worksheet, the cell menu, or the ribbon. See 10 different ways to run a macro in this other page. The below macro is used to open the form. We could set the value for any property of the form or its controls, for example, we could populate the combo box from within this macro too (but we have already included that in the Userform initialization, either way is valid).

 Sub ShowForm()
     With projectForm
         'set properties of Userform or controls (if needed)
         .Show
     End With
 End Sub

Most actions are associated with the click of a button; that’s the most common case. However, we could very well associate a macro to any other event, for example right after updating the value in a text box, or when selecting a value in the combo or list box. The macro ShowAlert is called from the Change event procedure of the combo box. When the value in the combo box changes, the macro prompts the message associated with the selected RAG status (when project is amber or red).

 Sub ShowAlert()
     Dim RAGstatus As String
     RAGstatus = projectForm.cbRAG.Value
     If RAGstatus = "Red" Then
         MsgBox "Red project - inform manager"
     ElseIf RAGstatus = "Amber" Then
         MsgBox "Amber project - keep on watch"
     End If
 End Sub

Two of the buttons call the two macros below: AddProject and SaveProjects. The third button is closing the form and that one line of code stays in the Userform module. AddProject gets the project ID from the text box (tbProject) and the RAG status from the combo (cbRAG), and adds the concatenation as a line entry in the list box (lbProjects).

 Sub AddProject()
     Dim projectID As String, RAGstatus As String
     With projectForm
         projectID = .tbProject.Value
         RAGstatus = .cbRAG.Value
         If projectID <> "" And RAGstatus <> "" Then
             .lbProjects.AddItem projectID & ": " & RAGstatus
         Else
             MsgBox "Enter a project and select the RAG status"
         End If
     End With
 End Sub

The other macro (SaveProjects) writes the contents of the list box (lbProjects) to the active worksheet. Specifically, it loops through each item in the list and adds the values to one cell after another along column A.

 Sub SaveProjects()
     Dim i As Integer, r As Integer
     With projectForm
         For i = 0 To .lbProjects.ListCount - 1
             r = r + 1
             ActiveSheet.Cells(r, 1).Value = .lbProjects.List(i)
         Next i
     End With
 End Sub

<<< Previous | Next >>>