Excel Simple Registration Form Controls

In a previous post we have seen how to create a simple registration system in Excel using basic VBA macros. Such system consists of a registration form where users can enter the data (an onboarding form with the onboarding date, employee name, role, etc., in this example), and a registry that stores all the entries. In this post we see how to use form controls to create the registration form instead of using a range of cells. We will specifically be using ActiveX controls in Excel, which are programmable controls that allow to add functionality to the form.

 

Registration Form

The registration form consists of a group of ActiveX controls including labels, text boxes, combo boxes, and command buttons, added the worksheet (Sheet1). We can change the value or caption of the controls, or the position, font size, color, etc, in the properties window upon control right-click. The name of some of the controls has been updated for easier handling. For example, the combo boxes for day or month are now comboDay and comboMonth, instead of ComboBox1 and ComboBox2. The department field has also been put in a combo box (comboDept). The name of the text boxes has been changed to tbYear, tbName, tbRole, and tbManager. The command buttons are cbSend, cbClear, and cbReg.

The registration form needs to be initialized to populate the drop-down list of the combo boxes, and add any initial predefined values such as the current date for example. The best approach is to initialize the controls on file open through a Workbook_Open event procedure. The following line of code populates the list of months from the corresponding custom list available in Excel.

  Sheet1.comboMonth.List = Application.GetCustomListContents(4)

 

We can choose to add the current date when opening the file. Once we know the current month and year, we can get the number of days in that month with the DateSerial function as indicated in the code below.

 

  Dim todayis As Date, thisyear As Integer, thismonth As Integer, d As Integer

 

  todayis = Now()

  thisyear = Year(todayis)

  thismonth = Month(todayis)

 

  With Sheet1

    .tbYear.Value = thisyear

    .comboMonth.List = Application.GetCustomListContents(4)

    .comboMonth = MonthName(thismonth)

    monthdays = Day(DateSerial(thisyear, thismonth + 1, 1) - 1)

   

    For d = 1 To monthdays

      .comboDay.AddItem (d)

    Next d

    .comboDay = Day(todayis)

  End With

 

 

Send Form

In order to send the registration form, we need to program the click event of the corresponding command button. That’s shown per default when selecting the control in the sheet module. We can associate the controls to several other events depending on the type of control. Generally, most controls accept a change event, click, double-click, setting and losing focus events, key up, down, press, or mouse up, down, and move events.

 

The macro “SendForm” runs when clicking that command button and puts the registration data from the text and combo boxes into variables. We just need to make sure to refer to the worksheet where the controls were added (in this case is Sheet1).

 

  With Sheet1

      oDate = .comboDay.Value & "/" & .comboMonth.Value & "/" & .tbYear.Value

      oName = .tbName.Value

      oRole = .comboRole.Value

      oDept = .comboDept.Value

      oManager = .tbManager.Value

  End With

 

 

Then it writes the data to the corresponding row in the other sheet (the registry). We need to get the last row with content (lr) in that sheet so that the entries are added to the first available row.

 

  With Sheet2

      lr = .Cells(Rows.Count, "A").End(xlUp).Row

      .Range("A" & lr + 1).Value = oDate

      If lr > 1 Then prevID = .Range("B" & lr).Value Else prevID = "34530"

      .Range("B" & lr + 1).Value = prevID + 1

      .Range("C" & lr + 1).Value = oName

      .Range("D" & lr + 1).Value = oRole

      .Range("E" & lr + 1).Value = oDept

      .Range("F" & lr + 1).Value = oManager

  End With

 

 

Clear Form

This macro just clears the contents in certain fields (we may want to leave the date though). The macro is called from a click event applied to the corresponding command button, and clears the value in the respective text and combo boxes in Sheet1.

 

  With Sheet1

      .tbName = ""

      .comboRole = ""

      .comboDept = ""

      .tbManager = ""

  End With

 

 

Similarly, we can assign the click event of the last button (Go To Registry) to activate the registry worksheet (in this example that’s Sheet2).

 

Update Days

Additionally, we probably want to update the combo-box with the list of days whenever the month or year changes. We need to add the “Change” event to the month and year combo-boxes to trigger the macro below, which calculates the number of days for the selected month and year in the same way it’s done when initializing the form (explained earlier).

 

  Sub UpdateDays()

      Dim yearis As Integer, monthis As Integer, monthdays As Integer, d As Integer

      With Sheet1

          monthnameis = .comboMonth.Value

          yearis = .tbYear.Value

          monthis = Month("1/" & monthnameis & "/" & yearis)

          monthdays = Day(DateSerial(yearis, monthis + 1, 1) - 1)

          .comboDay.Clear

   

          For d = 1 To monthdays

              .comboDay.AddItem (d)

          Next d

          .comboDay = 1

      End With

  End Sub

 

 

The ActiveX form controls added to the worksheet work in a similar way than Userform controls. In the next post we will see how to create the registration system through a Userform, which is probably the best approach from all three. Excel Userforms are very powerful and allow to further add functionality to the registration process. They are user friendly and ensure that the contents in the worksheet are not altered.

 

Click Here to download Excel Simple Registration with Form Controls.