Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

Excel VBA ComboBox: Important Properties to Explore

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 hide

Download Practice Workbook

To practice by yourself, download the following workbook.


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


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

Read More: Insert ComboBox with Listindex in Excel VBA (with Easy Steps)


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

Read More: How to Create a Searchable ComboBox with VBA 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: How to Use ListFillRange Property of ComboBox in Excel


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.

Read More: How to Use VBA to Populate ComboBox List from Range in Excel


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

Read More: How to Use ComboBox to Get Selected Item Utilizing VBA


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


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


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. To read similar articles, check out the ExcelDemy website. Lastly, please use the comment section below to post any questions or make any suggestions you might have.


Related Articles

Sudipta Chandra Sarker

Sudipta Chandra Sarker

Hello! Welcome to my Profile. Currently, I am working and doing research on Microsoft Excel. Here I will be posting articles related to this. My educational degree is BSc in Electrical and Electronic Engineering from Bangladesh University of Engineering and Technology, Bangladesh. I have a great interest in research and development. I always try to gather knowledge from various sources and try to make innovative solutions.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo