Excel Simple Registration System

This post showcases a simple but very useful registration system in Excel. It is a classic when learning Excel VBA, and consists of a registration form where users can enter some data, and a registry list that stores all the entries. As an example, we have an on-boarding registration system, but this could apply to any other registration process such as purchase order submission registration, invoicing, incidents, etc. In this example, the registration form is in the first worksheet and the registry in another worksheet, but it could all be in the same worksheet too.

Registration Form

The registration form consists of a two-column range with the name of each field in the first column, and the input cells to enter the data in the second column. This is the easiest way to receive input in Excel, just typing the values into the cells. Alternatively, we could use form controls on the worksheet to create the form, or enter the data through a UserForm, or do it online through a Google Form, for example. We will see all that in later posts. In this post, we will focus on the simplest way to do it.

There are several buttons associated with the respective macros to clear the form, submit the form, add fields to the form, and, when submitting, there’s a possibility to choose whether to clear the form when submitted or not. Now let’s see how to code each of the macros associated with the buttons to clear the form, submit the form, and add a new field to the form.

 

Clear Form

This macro just clears the contents in the input cells. In this example, that’s in column C and starts in cell C4. But the range could change if we add fields to the form, and therefore, we will first get the last row with content in column B, which contains the fields, and we put that into a variable (lr). After that, we clear the contents in column C for as many fields as there are.

  

  Private Sub ClearForm()

      Dim lr As Integer

      With Sheet1

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

         .Range("C4:C" & lr).ClearContents

      End With

  End Sub

 

 

Note that we refer to Sheet1 in this macro. That’s the worksheet object name that appears in the VBA project explorer. It may be different than the worksheet (tab) name. In this example, the worksheet name is “Form”, and that could be changed by the user unless protecting the workbook structure. Therefore, it is better to refer to the worksheet object name instead. That one can only be changed in the VBA editor properties window (if the VBA project is not protected).  

 

Submit Form

In order to submit the registration form in Excel, we also need to get the last row with content to know how many rows (fields) we have to submit. So the first lines of code are similar to the previous macro. Then we copy the range from C4 until the last field in column C (in this example is C9).

While the previous happens in Sheet1, now we are going to work with Sheet2 (the registry worksheet) to paste the data. To do that, we need to get the last row with content in Sheet2, and paste the data after the last row with data. We are using the PasteSpecial method along with Transport to copy data from a vertical alignment in the form to a horizontal alignment in the registry. Additionally, we can activate Sheet2 if we want to move to the registry and see the new entry every time we submit the form.

 

  Private Sub SubmitForm()

      Dim lr As Integer

      With Sheet1

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

          .Range("C4:C" & lr).Copy

      End With

 

      With Sheet2

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

         .Range("A" & lr + 1).PasteSpecial Transpose:=True

         .Activate  'optional

      End With

 

      Application.CutCopyMode = False

 

      If Sheet1.CheckBoxes("Check Box 1").Value = 1 Then ClearForm

  End Sub

 

 

When copying a range in Excel, it highlights the range with a dashed line. In order to get rid of that, we set the CutCopyMode property of the Application object to false. We may want to clear the form after submission. In that case, we just need to call the ClearForm macro. In this example, we have added a check box to let the user decide whether to clear the form after submission or not. The last line of code in the macro above checks if the corresponding option has been selected in the check box to clear the form

 

Add New Field

We may want to add new fields to the form. We could easily do that manually, but for other users it may be more convenient to add a button to do so. That will also prevent from altering the structure of the form. We may also want to protect the worksheet (the form) and allow to add fields only through this button (in that case the code should be tweaked to unprotect the worksheet first).

As done previously, we need to get the last row with content, and then insert a row after that. As that will not take the format of the form, we need to add borders to the new two cells of the form. Then we can add manually the relevant name of the field.

 

  Private Sub AddField()

      Dim lr As Integer

      With Sheet1

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

          .Rows(lr + 1).Insert

          .Range("B" & lr + 1 & ":C" & lr + 1).Borders.LineStyle = xlContinuous

      End With

  End Sub

 

 

Function to get Last Row

The macros above show how to do each of the tasks individually. But when we put it all together, it makes sense to create a common function to get the last row with content, and use it across all macros. This reduces the amount of code and time too, and makes things easier if we need to change something later in that function. The function accepts a Worksheet object variable as parameter, in order to get the last row with content in the relevant worksheet. The function returns an integer value with the row number.

 

  Private Function LastRow(sh As Worksheet) As Integer

      LastRow = sh.Cells(Rows.Count, "B").End(xlUp).Row

  End Function

 

 

Then, we can replace lr in all the macros above with LastRow(sheet), specifying the sheet where we want to get the last row with content in parenthesis. That saves a few lines of code in each macro. For more complex projects, that could save a lot of code. Find the full code in the file attached.

 

Click Here to download Excel Simple Registration.