Excel VBA ComboBox: Important Properties to Explore

Get FREE Advanced Excel Exercises with Solutions!

In this article, we will explore VBA ComboBox properties in Excel. ComboBox is a drop-down list that combines textbox and list. To access the full advantage of the ComboBox in Excel, we need to know the properties of the box. So, without the delay, let’s start the discussion and learn the VBA ComboBox properties in Excel.


Table of Contents Expand

How to Add VBA ComboBox in Excel

You can add the ComboBox in Excel using 2 ways. You can easily add ComboBox by ActiveX Control or by creating UserForm. We will create our ComboBox with the UserForm. Let’s see the steps below to learn the method.

  • Firstly, you have to select Visual Basic from the Developer tab.

How to Add VBA ComboBox in Excel

  • After that, the Microsoft Visual Basic Application window will open up.
  • Then, select UserForm from the Insert tab to create a form.

  • Now, you can see a UserForm and a Toolbox.
  • Then, select the following icon from the Toolbox.

  • After selection, you can see a plus (+) sign.
  • Now, put the icon on the form.
  • You need to put the icon in the place where you want to show your ComboBox.
  • Then, resize the ComboBox by dragging the (+) icon.

How to Add VBA ComboBox in Excel

  • Finally, you can see a ComboBox.
  • Therefore, we have been able to add ComboBox in Excel.


23 Important Properties of VBA ComboBox in Excel

For exploring VBA ComboBox properties in Excel, we will use a dataset of the mark sheet of a class. There is information about Student ID, Student Name, and marks in Mathematics, Physics, and Chemistry. We have also added a ComboBox to access the dataset.

excel vba combobox properties


Let’s follow the sections below to explore VBA ComboBox Properties in Excel.

1. Alphabetic/Categorized Menu Bar of Excel VBA ComboBox

  • Firstly, select and right-click on the ComboBox.
  • Then, a menu will open up and select Properties from here.
  • After that, the Properties window will appear.
  • Now, got to the properties of the ComboBox.
  • At the upper side of the box, there are 2 tabs available.
  • One is Alphabetic and the other is Categorized.
  • In the Alphabetic tab, you can observe the properties in alphabetic order.

Alphabetic/Categorized Menu Bar of Excel VBA ComboBox for Properties

  • In the Categorized tab, you can observe the properties in group order.
  • In the Appearance group, the properties are related to that group.
  • Here, the groups are arranged in alphabetic order.

Alphabetic/Categorized Menu Bar of Excel VBA ComboBox for Properties


2. Name VBA ComboBox Using Properties Window in Excel

  • We can manually set the name of the ComboBox by typing the name in the (Name) section.
  • You can give the name as per your wish.
  • So, we have named the ComboBox as studentinfo.

Name VBA ComboBox Using Properties Window in Excel


3. RowSource Property of Excel VBA ComboBox to Access Cell Range

  • In the following step, we need to refer the cells to the ComboBox.
  • For that purpose, go to the RowSource property.
  • Then, write the sheet name, “!” and cell range as the following example.
  • We have set our sheet name as Properties and our dataset is in B4 to F14 cell range.
  • Therefore, we have written Properties!B4:F14.

RowSource Properties of Excel VBA ComboBox to Access Cell Range

  • After that, click on the drop-down menu of the ComboBox.
  • Now, we can observe that ComboBox is referring to our main dataset.

RowSource Property of Excel VBA ComboBox to Access Cell Range

Read More: Create ComboBox with RowSource in Excel VBA


4. Set Input Style of VBA ComboBox with Style Property in Excel

  • Now, we will explore the style property of the ComboBox.
  • So, go to Style and select 0-fmStyleDropDownCombo.

Set Input Style of VBA ComboBox with Style Property in Excel

  • After that, to observe the change go to ComboBox.
  • You can manually enter any information here.
  • You can type 1606001 instead of selecting it from the drop-down menu.

Set Input Style of VBA ComboBox with Style Property in Excel

  • After that, in the Style property, change the option.
  • Here, we will select 2-fmStyleDropDownList.

Set Input Style of VBA ComboBox with Style Property in Excel

  • But, in the ComboBox, we can not manually give input.
  • So, we have to select the data from the drop-down menu.


5. DropButtonStyle Property of VBA ComboBox to Change Drop-Down Icon

  • In the next step, we will observe the DropButtonStyle property.
  • Now, find out DropButtonStyle and there are 4 types of styles available.

DropButtonStyle Property of VBA ComboBox to Change Drop-Down Icon

  • The design of the drop-down icon can be changed by this property.
  • We have selected DropButtonStyleEllipsis.
  • Therefore, the drop-down icon is changed to the following format.
  • You can further change the style by changing the parameters.

DropButtonStyle Property of VBA ComboBox to Change Drop-Down Icon


6. ListStyle Property to Change Style of ComboBox Option

  • In the next step, we will observe the ListStyle property.
  • There are 2 types of styles available in the ListStyle property. You can view this by clicking on the drop-down icon in the ListStyle section.
  • The options of the ComboBox will show the Radio Box icon by selecting 1-fmListStylePlain.
  • And the options of the ComboBox will not show anything if we select 0-fmListStyleOption.

ListStyle Property to Change Style of ComboBox Option

  • We have selected 1-fmListStylePlain and the options of the ComboBox will show the Radio Box icon.

Excel VBA ComboBox Properties


7. ColumnCount Property to Increase Number of Visible Columns Inside ComboBox

  • In this section, we will explore the ColumnCount property.
  • The ColumnCount indicates the number of columns visible inside the ComboBox.
  • By default, it is given 1 and we will change it to 2.

ColumnCount Property to Increase Number of Visible Columns Inside ComboBox

  • After that, move to the ComboBox and click on the drop-down icon.
  • As a result, we can now see 2 columns of data instead of 1.
  • We can show 3 or 4 columns here by typing ColumnCount as 3 or 4.


8. ColumnWidths Property to Change Width of ComboBox Columns

  • We can also change the width of the ComboBox by using the ColumnWidths property.
  • At this moment, we are working with columns.
  • So, we need to set 2 widths for each column.
  • We have taken the widths as 32pt;32pt.
  • We can also change the widths as per our wish.
  • Also, you can put the width in cm or inch format.

ColumnWidths Properties to Change Width of Excel VBA ComboBox

  • Finally, you can observe the result.
  • The column width of the ComboBox menu is decreased.


9. Update Assigned Value of ComboBox Using BoundColumn Property

  • In the following step, we will explore the BoundColumn property.
  • BoundColumn indicates the n-th column value of the box.
  • By default, this value is 1 and the ComboBox value is showing the 1st column.
  • So, set the BoundColumn value as 2.
  • In the Value property, it is showing James.
  • From the dataset that we introduced at the beginning, it is understandable that James is in the 2nd column.
  • We can change the column value by changing the property value.

Update Assigned Value of ComboBox Using BoundColumn Property

Read More: Excel VBA ComboBox Value


10. TextAlign Property to Change Alignment of VBA ComboBox

  • In this section, we will explore the TextAlign property.
  • This property is used mainly to change the text alignment of the heading of the ComboBox.
  • We can put the heading in left, center, or right alignment as per our wish.
  • So, we have selected 2-fmTextAlignCenter.

TextAlign Property to Change Alignment of VBA ComboBox

  • After that, go to the ComboBox.
  • As a result, you can see that 1606003 is showing in the middle.


11. Width Property for Changing Excel ComboBox Width

  • Using the Width property, we can change the width of the ComboBox.
  • We have selected the property value as 160.
  • You can increase or decrease the width by changing this property value.

Width Property for Changing ComboBox Width


12. TextColumn Property to Update Assigned Text of VBA ComboBox

  • TextColumn property shows Text on the ComboBox.
  • Normally, this property value is given as 1.
  • We have set the value 2 and it is indicating the 2nd column of the dataset we introduced at the beginning of the article.
  • Therefore, in the Text property, it is showing the 2nd column’s data, James.

TextColumn Property to Update Assigned Text

  • Then, return to the ComboBox and it will show James.


13. Change ComboBox Color Using BackColor Property

  • BackColor property indicates the color of the ComboBox.
  • Using the drop-down menu, you can select your favourite color.
  • Now, we have selected the following color.

Change ComboBox Color Using BackColor Property

  • After that, return to the ComboBox and you can see that the color of the ComboBox is changed.


14. Set Different Border Color Using BorderColor Property

  • Like the previous properties, you can change the border color by using the BorderColor property.
  • So, go to the BorderColor property and change according to your wish.

Set Different Border Color Using BorderColor Property


15. Access Headers of Column with ColumnHeads Property

  • Now, we will explore the ColumnHeads properties of ComboBox.
  • Generally, it is set to False.
  • So, set True in the value section of the property and observe the result.

Access Headers of Column with ColumnHeads Property

  • As a result, the header will be visible on the top of the ComboBox options.
  • You did not select the header range from the dataset to the ComboBox.
  • But, the header is showing on the top of the ComboBox options.
  • You can turn off the header by resetting the value to False.


16. Fit ComboBox Size Using AutoSize Property in Excel

  • In this section, we will take a look at the AutoSize property.
  • This property automatically fits the size.
  • So, we can change the size manually but we will do this task automatically.
  • To do so, set the value of AutoSize as True.
  • As a result, the size of the ComboBox is modified.

Fit ComboBox Size Using AutoSize Property


17. Change ComboBox Font Using Font Property

  • In the following step, we will change the font of the ComboBox.
  • For that purpose, go to the Font property and click the drop-down icon.
  • After that, select the font you want to set.
  • We have selected the Times New Roman font of size 12.
  • Then, click OK and observe the modification in the ComboBox.

Change ComboBox Font Using Font Property

  • As a result, Font and Font Size are changed.


18. Enable or Disable ComboBox in Excel Utilizing Enabled Property

  • In the present section, we will analyze the Enabled option.
  • By default, the value of the Enabled property is True.
  • True means the ComboBox is active.
  • Similarly, we can turn off the ComboBox by selecting False.

Enable or Disable ComboBox Utilizing Enabled Property


19. Change ComboBox Position in Excel with Height and Left Properties

  • In the following step, we will explore the Height and Left properties.
  • We can insert the value of Height and Left as per our requirements.
  • Height property indicates the height of the ComboBox.
  • And Left property indicates the position of the box from the left corner.

Change ComboBox Position with Height and Left Properties


20. Limit ComboBox Elements Using ListRows Property

  • At this moment, we will demonstrate the ListRows property.
  • Before, the property value was 8.
  • Therefore, in the ComboBox menu, there were 8 elements inside the ComboBox.
  • In this case, we have set the value 5.

Limit ComboBox Elements Using ListRows Property

  • As a result, 5 rows are visible on the ComboBox.


21. MatchRequired Property to Maintain List Integrity in VBA ComboBox

  • In this section, we will work with the MatchRequired property.
  • By default, the property value is set as False.
  • After selecting the Style property as 0-fmStyleDropDownComb, we can take advantage of the MatchRequired property.
  • Also, we need not fully match the elements at the time of writing at the ComboBox.
  • Suppose, we will show the 2nd column in the header of the combobox.
  • So, we want to write Olivia. We can start writing 1 or 2 letters of Olivia and suggestions will be shown in the ComboBox if the property value is True.
  • Otherwise, suggestions will not be shown and we need to write the full name here.

MatchRequired Property to Maintain List Integrity


22. ShowDropButtonWhen Property to Show or Hide Drop-Down Menu of ComboBox

  • At this moment, move to the ShowDropButtonWhen property.
  • This property indicates when the button of the drop-down menu will be visible.
  • The drop-down menu button will be hidden if we select 0-ShowDropButtonWhenNever.
  • Also, you can explore other properties.
  • You can turn on the button at all times or only at the time of work.

ShowDropButtonWhen Property to Show or Hide Drop-Down Menu

  • In the following step, move to ComboBox to observe the results.
  • We have selected 0-ShowDropButtonWhenNever and ComboBox is not showing the button.


23. SpecialEffect Property to Display Special Effects on Excel VBA ComboBox

  • Finally, we have moved to our last property.
  • Here, we will show the SpecialEffect property.
  • So, find out SpecialEffect in the Properties box and you can 5 options here.
  • These options play with the outlook of the ComboBox.
  • Therefore, you can change the outlook by selecting the options.

SpecialEffect Property to Display Special Effects on ComboBox


Download Practice Workbook

To practice by yourself, download the following workbook.


Conclusion

In this article, we have demonstrated the properties of VBA Combo Box in Excel. Furthermore, there is a practice workbook at the beginning of the article. So, go ahead and give it a try. Lastly, please use the comment section below to post any questions or make any suggestions you might have.


Related Articles

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.

Tags:

Sudipta Chandra Sarker
Sudipta Chandra Sarker

Sudipta Chandra Sarker, BSc, Electrical and Electronic Engineering, Bangladesh University of Engineering and Technology, Bangladesh, has worked on the ExcelDemy project for over a year. For ExcelDemy, he has authored 42 articles and reviewed over ten articles. He is employed as a junior software developer at the moment. He aims to create various useful Microsoft Office Add-ins, extending the functionality of Office programs. His interests span Microsoft Office Suites, Data Science, VBA, VB.NET, ASP.NET, C#, Excel, and Desktop... Read Full Bio

4 Comments
  1. Como habilito siquiero hacer una seleccion multiple del control

    • Gracias César por tu consulta. Lamentablemente, no podemos seleccionar varias opciones en un ComboBox. Sin embargo, si necesita seleccionar varias opciones, puede utilizar el ListBox que tiene la propiedad MultiSelect. Pero un inconveniente de ListBox es que no permite a los usuarios escribir directamente en él. Para obtener más información sobre MultiSelect ListBox, puede leer este artículo.
      Saludos
      Aniruddah
      Equipo Exceldemy

      [Thanks Cesar for your query. Unfortunately, we can not select multiple options in a ComboBox. However, if you need to select multiple options, you can use the ListBox that has the MultiSelect property. But, one drawback of ListBox is that it doesn’t allow the users to directly write on it. To learn more about MultiSelect ListBox, you can read this article.

      Regards
      Aniruddah
      Team Exceldemy]

  2. Great information. Regarding the rowsource data, I am trying to reference a particular column in a table of data to populate the combobox. I have gotten it to accept the table (=Sheetname!TableName) but I cannot figure out how to specify a particular column of the table. (My table has several columns)
    I have tried adding [Column] with no luck.

    • Reply Lutfor Rahman Shimanto
      Lutfor Rahman Shimanto Feb 4, 2024 at 5:51 PM

      Hello SCOTT

      Thanks a lot for your kind words. You want to specify a particular column from a table in Excel VBA ComboBox’s RowSource property.

      Output Overview:

      Let’s assume you have a table named Data in the sheet Database with several columns, and you want to populate your ComboBox with the values from a specific column, let’s say the Project column.
      You can use the following format for the RowSource property: =Database!Data[Project]

      Hopefully, the idea will help. I have also attached the solution workbook for better understanding. Good luck.

      DOWNLOAD SOLUTION WORKBOOK

      Regards
      Lutfor Rahman Shimanto
      Excel & VBA Developer
      ExcelDemy

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo