Drop-down list (2)

I have already added the code for the “Add” button, the drop-down list and the “Close” button. Let me go through this once more.

The code to be executed when you click the “Close” (cmdClose) button is in the lower part (3).

In the first part, we find the code to be executed when the “Add” button (cmdAdd) is clicked (1).

With the first line of code, ActiveWorkbook.Sheets (“Department”).Activate, we activate the worksheet “Department”. With the second line of code, Range (“A1”).Select, we select cell A1.

The following lines of code are searching for a blank cell and, when found, write the data in the blank cells.

Do

If IsEmpty (ActiveCell) = False Then
ActiveCell.Offset (1, 0). Select
End If
Loop Until IsEmpty (ActiveCell) = True
ActiveCell.Value = txtName.Value
ActiveCell.Offset (0, 1) = cboDepartment.Value
Range (“A1”). Select

Here is what it does:

If the active cell is not empty (If IsEmpty (ActiveCell) = False Then)
Then the next row is selected (ActiveCell.Offset (1, 0). Select)
It repeats this until an empty cell is found (Loop Until IsEmpty (ActiveCell) = True)
When an empty cell is found, the data from the box txtName in placed in the active cell:

ActiveCell.Value = txtName.Value

Next, it jumps to the right of this cell and inserts the data from the drop-down menu in this cell:

ActiveCell.Offset (0, 1) = cboDepartment.Value

Then it returns to cell A1 in the worksheet. (This doesn’t have to be specified as this only just jumps back to cell A1) –

Range (“A1”). Select

Next, all the boxes are emptied again (It is not necessary you do this either but this erases and clears all the boxes neatly when the data is written.

Me.txtName.Value = “”

Me.cboDepartment.Value = “”

Finally, it makes an end to the cmdAdd button subroutine with the End Sub command.

Then we have the code for the drop-down list (2).

As you will have noted in the previous VB code (1) for the button cmdAdd, there is no value assigned to the box “cboDepartment”. The only thing we have done here is to select the cell that is one cell to the right of the selected cell ActiveCell.Offset (0, 1) = cboDepartment.Value in our worksheet.

For this, we have the code that initializes the form, Private Sub UserForm_Initialize ()

The first line repeats the value of the trade txtName, or rather, this line does nothing, because there are no entries for the value.

But with the lines below, we determine the contents of the drop-down list cboDepartment:

With cboDepartment

. AddItem “Administration”
. AddItem “Design”
. AddItem “Advertising”
. AddItem “Sales”
End With

and the value of the field cboDepartment

cboDepartment.Value = “”

Date

And as a final example that I’m going to add a date to the form by means of the DTPicker control (1). This control is provided by default in the toolbox, but when you right-click this, it displays a list (2) with dozens of other controls.

Select “Microsoft Date and Time Picker control” (3) in the list, and click OK.
In versions before 2007, this control is called “Calendar Control”. The layout was somewhat different, but the code we need to add to VBA remains the same.

Once the calendar is added to the form, click the “View” button and select “Code”. In the VBA code we need to add two more lines of code. The subroutine for the DTPicker has already been added.
Here you type the code Me.DTPicker1.Value = Date (1). Next we must add an additional line to the subroutine of the button cmdAdd. This jumps to the cell next to the selected cell, “ActiveCell.Offset (0, 2) = DTPicker1.Value” (2), because it places this value in the column next to the department column.

Test the code now.

Featured Courses

• Downloading iPad Apps
• Lesson 1: Introduction to Publisher
• Lesson 65: File Formats