Last Updated on January 16, 2023 by token
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.
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.
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
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.
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:
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:
