ActiveX Controls – ComboBox in Excel VBA

VBA Combo Box in Excel SpreadSheet

Inserting VBA Combo Box (ActiveX Control)

The VBA ComboBox is a drop-down list that you can use in Excel. Remember, that this is not the same type of list that you can enter in Excel for data correctness. As in the case of a VBA TexBox, to insert a VBA ComboBox, go to the Developer tab. Turn on the design mode, then select the ComboBox from ActiveX controls.

Inserting VBA Combo Box (ActiveX Control)

After inserting Combo Box, check its Properites right-clicking on box, which you inserted on Excel Sheet. First value is the system name of your Combo Box. It will be using in programming this element.

Inserting VBA Combo Box (ActiveX Control) – Properties window

VBA ComboBox programming (ActiveX control)

As in the case of a text box, combo boxes should be properly programmed. The combo boxes are programmed as follows:

  • Go to the VisualBasic Editor and select the “This Workbook” object on the left or “This Workbook” depending on the version of Excel
  • In the Code window we set the “Workbook” object and the “Open” trigger. Thanks to this, the values ​​in the combo boxes will be loaded every time you open the workbook.
  • To enter values ​​into a combo box list, we refer to the appropriate sheet and combo box as SheetName.CombiName “Value that should appear in the field”. In this way, we enter all our values ​​in the following lines of the code, e.g. voivodeships. In our case, the name of the combo box will be ComboBox1
  • It is important that you save, close and reopen the Excel file each time you edit the value in the combo box.
Private Sub Workbook_Open ()

Arkusz1.ComboBox1.AddItem "dolnośląskie"
Arkusz1.ComboBox1.AddItem "kujawsko-pomorskie"
Sheet1.ComboBox1.AddItem "lubelskie"
'(...)

End Sub
Excel VBA Course - ComboBox Combo
Excel VBA Course – ComboBox Combo
Excel VBA Course - ComboBox Combo
Excel VBA Course – ComboBox Combo

If you are using a UserForm and not controls embedded in a worksheet, be sure to read one of the next chapters of the VBA course on inserting a ComboBox in a UserForm .

ComboBox VBA programming (ActiveX control) – With clause

If there are many values, we should use the With clause . We only refer to combo box and worksheet this way once and save time writing our code

 Private Sub Workbook_Open ()

With Sheet1.ComboBox1
.AddItem "dolnośląskie"
.AddItem "kujawsko-pomorskie"
.AddItem "lubelskie"
.AddItem "lubuskie"
.AddItem "łódzkie"
.AddItem "małopolskie"
.AddItem "mazowieckie"
.AddItem "opolskie"
.AddItem "podkarpackie"
.AddItem "podlaskie"
.AddItem "pomorskie"
.AddItem "śląskie"
.AddItem "świętokrzyskie"
.AddItem "warmińsko-mazurskie"
.AddItem "wielkopolskie"
.AddItem "Zachodniopomorskie"
End With

End Sub

The code entered in the above way should be closed using the End With expression . It is also important to save, close and reopen the Excel file each time you edit the value in the combo box.

Excel VBA Course - ComboBox Combo
Excel VBA Course – ComboBox Combo

VBA ComboBox programming (ActiveX control) – using a loop

At this point, we can already use the combo box functionality. If we want to program successive fields corresponding to the day, month and year of birth, the easiest way is to use the For … Next loop . If we want the ComboBox2 field responsible for the day of birth to display values ​​from 1 to 31 like the days of the month, we should program the combo box as follows:

With Sheet1.ComboBox2
Dim intLicznikBox2 As Integer
For intLicznikBox2 = 1 To 31
.AddItem intCountBox2
Next
End With

The combo box is programmed as before in the ” This workbook ” object code . Displaying the days of the week as numbers from 1 to 31 is of course a simplification, as not every month has so many days. We can program the code of our application in such a way as to eliminate such cases. To complete filling the values ​​in combo boxes 3 and 4, we program the code responsible for the month and year in the same way. For a change, let’s also use a While..Wend loop to create values ​​in our fields. Our program code in the ” This Workbook ” object should look like this:

Private Sub Workbook_Open ()

With Sheet1.ComboBox1
.AddItem "dolnośląskie"
.AddItem "kujawsko-pomorskie"
.AddItem "lubelskie"
.AddItem "lubuskie"
.AddItem "łódzkie"
.AddItem "małopolskie"
.AddItem "mazowieckie"
.AddItem "opolskie"
.AddItem "podkarpackie"
.AddItem "podlaskie"
.AddItem "pomorskie"
.AddItem "śląskie"
.AddItem "świętokrzyskie"
.AddItem "warmińsko-mazurskie"
.AddItem "wielkopolskie"
.AddItem "Zachodniopomorskie"
End With

With Sheet1.ComboBox2
Dim intLicznikBox2 As Integer
For intLicznikBox2 = 1 To 31
.AddItem intCountBox2
Next
End With

With Sheet1.ComboBox3
Dim intLicznikBox3 As Integer
intCountBox3 = 1
While intLumerBox3 <= 12
.AddItem intCountBox3
intCountBox3 = intCountBox3 + 1
Wend
End With
With Sheet1.ComboBox4
Dim intLicznikBox4 As Integer
For intLicznikBox4 = 1980 To 2000
.AddItem intCountBox4
Next
End With

End Sub

And this is the result of our script:

Excel VBA Course - ComboBox Combo
Excel VBA Course – ComboBox Combo

Programming a ComboBox (ActiveX control) – Entering values ​​from a ComboBox

To enter data from our combo boxes into the table, we should enter the reference to the ComboBox elements 1,2,3,4 in the code of the Sheet1 object. In the case of fields responsible for the day, month, year, we should use the DateSerial () function  to create a date from our elements. After modifying the code, we can test it. Remember that we are editing this part of the code in Sheet1.

Private Sub CommandButton1_Click ()

Dim sngId As Single
Dim sngWiersz As Single
Dim sngLogin Counter of As Single

'SELECTING THE LAST UNSAVED LINE AND ID
sngId = 1 + Application.WorksheetFunction.Max (Range ("A: A"))
sngWiersz = 6 + Application.WorksheetFunction.CountA (Range ("A: A"))

'CHECK LOGIN
sngLogin Counter = 7

Do While sngLoginCount <= sngWiersz

If Cells (sngLoginCount, 2) = LCase (TextBox1) Then
MsgBox "This login is already in the database, please enter another", vbCritical
GoTo endlabel
End If

sngLoginCount = sngLoginCount + 1
Loop

'DATA INPUT
Cells (sngWiersz, 1) = sngId
Cells (sngWiersz, 2) = LCase (TextBox1)
Cells (sngWiersz, 3) = UCase (Left (TextBox2, 1)) & LCase (Mid (TextBox2, 2))
Cells (sngWiersz, 4) = StrConv (TextBox3, vbProperCase)
Cells (sngWiersz, 5) = LCase (TextBox4)
Cells (sngWiersz, 6) = ComboBox1
Cells (sngWiersz, 7) = DateSerial (ComboBox4, ComboBox3, ComboBox2)
endlabel:
End Sub

VBA ComboBox in Excel UserForm

How do you insert the ComboBox combo box into the UserForm form?

The combo box is already known to us. We got to know them while creating the application for entering data . Due to the fact that the field inserted in the UserForm form is programmed in a different place, we will create an example in which we will show how to program it. Select the ComboBox control from the ToolBox and put it to the right of the TextBox1 and TextBox3 fields in the first two tabs of our application. Our fields shouldto be called ComboBox1 and ComboBox2.

How do I program the ComboBox Combo Box list?

The next step is to program the list that will be displayed after clicking on the field. Unlike ActiveX controls inserted into the data sheet, the ComboBox field in the UserForm form is programmed directly in the UserForm form code. We go successively in the project window to the View Code view of the UserForm form. The most important thing is to set the UserForm object and the Initialize trigger in the Code window . As a result, the values ​​to the ComboBox field will load when the form is launched.

The code in the Initialize trigger is programmed in the same way as in the case of regular ActiveX controls. We set the values ​​right away for both combo boxes. The example below:

Private Sub UserForm_Initialize()

With ComboBox1
 .AddItem 5
 .AddItem 8
 .AddItem 23
End With

With ComboBox2
 .AddItem 5
 .AddItem 8
 .AddItem 23
End With
End Sub

Next, we should program the CommandButton buttons in the first two tabs of our program. Double click on the button in the “Netto gross” tab. The CommandButton1 button is programmed in the following way using the TextBox1, TextBox2 and ComboBox1 fields:

Private Sub CommandButton1_Click()
TextBox2 = TextBox1 + TextBox1 * (ComboBox1 / 100)
End Sub

In the above code, we have programmed the TextBox2 field after choosing the VAT rate. The analogous operation is performed for the CommandButton2 button in the “Gross on net” tab. All of our code in the UserForm object looks like this:

Private Sub CommandButton1_Click()
TextBox2 = TextBox1 + TextBox1 * (ComboBox1 / 100)
End Sub

Private Sub CommandButton2_Click()
TextBox4 = TextBox3 / (1 + (ComboBox2 / 100))
End Sub

Private Sub UserForm_Initialize()

With ComboBox1
 .AddItem 5
 .AddItem 8
 .AddItem 23
End With

With ComboBox2
 .AddItem 5
 .AddItem 8
 .AddItem 23
End With
End Sub

At the end of the “About the program” tab, you can enter any text using, for example, the Label label . Place the label content in the frame by selecting Frame earlier.

Below I attach a file with our program:

Leave a comment

Your email address will not be published. Required fields are marked *

%d bloggers like this: