{"id":12131,"date":"2012-03-25T08:46:00","date_gmt":"2012-03-25T08:46:00","guid":{"rendered":"http:\/\/www.learnexcelmacro.com\/?p=1491"},"modified":"2022-08-07T00:25:29","modified_gmt":"2022-08-07T00:25:29","slug":"excel-macro-tutorial-combobox-in-excel-macro","status":"publish","type":"post","link":"https:\/\/vmlogger.com\/excel\/2012\/03\/excel-macro-tutorial-combobox-in-excel-macro\/","title":{"rendered":"Excel Macro Tutorial : ComboBox in Excel Macro"},"content":{"rendered":"
It’s been a long time since I posted an article :(. Today I am going to write about Combo Box in Excel. At the end of this Article, you will be able to use Drop down in Excel. It will include Excel Macro Tutorial – Add Combo Box 1<\/p><\/div> Excel Macro Tutorial – Combo Box in Design Mode<\/p><\/div>\n Now as you can see that Drop Down is created in the Sheet, you will learn How to Add Options or Items in the Drop down.<\/strong> As of Now it is completely blank. There is no option\/item in it. To Add items by setting Properties of the Combo Box, follow the below Steps: Excel Macro Tutorial – Combo Box – ListFillRange Property<\/p><\/div> Excel Macro Tutorial – Drop Down List in Excel<\/p><\/div>\n In Drop Down List Box, all the Items are by default Indexed from 0, 1, 2 etc. The first item which is added to Drop Down List will be indexed as 0, Second One will be Indexed as 1 and so on.. Therefore no need of any indexing here while adding list items in the Drop Down List. In the Above Code, Sheet1<\/strong> is the Sheet Name where Combobox is there and ComboBox1<\/strong> is the Name of the ComboBox Control. Most of the time you add List Items dynamically. For Example:<\/strong> you have some list of Values stored in an Array Variable and you want all of them to Add in the Drop Down List. In the below Code, you will learn how to Add List Items from an Array Variable using For loop.<\/p>\n Take the Above Code and Paste it in a Regular Module and Run it after adding a Combobox in your Sheet. It will Add 10 Items in the Drop down from “Option 1” to “Option 10”.<\/strong> But there is a problem in Above code. If you run the same Code twice, in your Drop down there will be 20 list get added, if you run thrice then 30 and so on. Why? because as i told earlier that .AddItem<\/strong> always append the list. It does not clear the Previous ones and then add the new one. To overcome this Problem, we need to put a Statement to Clear all the Items before you add list in the Drop Down list box. Therefore in the below code, this problem will not occur.<\/p>\n \nAs you saw how to Clear or Remove All the Items from the Drop down.<\/strong> Now you will learn how to remove a particular Item from Drop Down List<\/strong><\/p>\n If you want to Remove a particular Item from the Drop Down then use the below code to remove item from the Drop Down List. For Removing an Item, you need to pass Index Number as an Input Parameter.<\/p>\n The Above Code will Always Remove the First List Item from the Drop Down. As you can see by default no List value is selected after adding items. If you want to make Some List Item as Default One then follow the below. \n If you want to Select the Last Item of the Drop Down List By default, then use the below Code:<\/strong> <\/p>\n Below is the Simple Code which will show you how to get the Selected Value of the Drop Down. .Value <\/strong> property of the Control ComboBox1<\/strong> returns the Selected Value of the Drop Down.<\/p>\n <\/p>\n <\/p>\n .ListCount<\/strong> is a Property in Of ComboBox object to get the Total Number of Items in a ComboBox. .ListCount<\/strong> always returns a Number. <\/p>\n
\n
\n1. Adding Items in Excel Combobox dor Excel Drop Down
\n2. Removing Items from Drop down
\n3. Removing All items from Drop Down
\n4. Selecting options from Drop down by position in the drop down
\n5. Selecting options from Drop down by Value of the option in the drop down
\n6. Excel Drop Down Default Value
\n7. How to get the Total Count of Items in a Combobox
\n
\n…..and many more<\/strong> things about Excel Combobox.
\n
\nFirst will learn how to place Drop Down or Combobox in your Excel Sheet.
\nBy going to Developer Tab –> Insert –> Combo Box Activex Control<\/strong><\/p>\n
\nNow Drag and Drop that Control any where you want in your Spreadsheet.
\nYou can Also re-size the Height and Width etc keeping it in Design Mode<\/p>\n
\nBasically there are two ways of Adding list Items in Drop Down List. 1. By Setting Range Formula in the Properties of Combo Box.
\n
\n2. By using Excel VBA Code<\/strong>
\n<\/p>\n Add List Items in Drop Down by Using Properties: <\/h3>\n
\n
\n Step 1.<\/strong> Select the Combo Box Control and Right Click and Open the Properties Window
\n Step 2.<\/strong> Now Enter the Cell Range in ListFillRange<\/strong> Property as shown below:<\/p>\n
\nNow whatever List Items you want to be there in the Drop Down, Type them in that Range in Excel Sheet. All the list available in that Range will start appearing in the Drop Down as shown below:<\/p>\n Add List Items in Drop Down by Using Excel Macro (VBA Code): <\/h3>\n
\nTo Add Item List in Drop Down List, there is a very Simple VBA Syntax as shown Below:
\n
\n<ComboBoxName>.AddItem <List Item> , <Index Number ><\/strong>
\n <\/p>\n\r\nSub Insert_Item_List()\r\n\r\nSheet1.ComboBox1.AddItem \"Option 1\", 0\r\nSheet1.ComboBox1.AddItem \"Option 2\", 1\r\nSheet1.ComboBox1.AddItem \"Option 3\", 2\r\nSheet1.ComboBox1.AddItem \"Option 4\", 3\r\nSheet1.ComboBox1.AddItem \"Option 5\", 4\r\n\r\nEnd Sub\r\n\r\n<\/code><\/pre>\n
\n
\nImportant:<\/strong>
\nIn Combobox, when you are adding a New Item, It always gets added at the End of the List Which are already assigned to that Drop Down List. It means, It always appends the items to the list. So Every time you want a fresh List of Items in your Drop down, then before adding any new Item, Clear all the Existing Items from the Drop Down. Below is the Code How to Clear All the Items from the Drop Down List<\/strong>
\n <\/p>\n\n
\n \n
\nSheet1.ComboBox1.Clear
\n \n<\/td>\n<\/tr>\n<\/table>\n\r\n\r\nSub Insert_Item_List()\r\n\r\nDim iCounter As Integer\r\nDim Item(10) As String\r\n'Store 10 List Items in an Array Variable\r\nFor i = 0 To 9\r\n Item(i) = \"Option \" & i + 1\r\nNext\r\n'Now Add all these items from Array Variable to Combo Box\r\nFor i = 0 To 9\r\n Sheet1.ComboBox1.AddItem Item(i)\r\nNext\r\nEnd Sub\r\n<\/code><\/pre>\n
\r\n\r\nSub Insert_Item_List()\r\n\r\nDim iCounter As Integer\r\nDim Item(10) As String\r\n'Store 10 List Items in an Array Variable\r\nFor i = 0 To 9\r\n Item(i) = \"Option \" & i + 1\r\nNext\r\n'Before Adding items to the List, Clear the Drop Down Box\r\n Sheet1.ComboBox1.Clear\r\n'Now Add all these items from Array Variable to Combo Box\r\nFor i = 0 To 9\r\n Sheet1.ComboBox1.AddItem Item(i)\r\nNext\r\nEnd Sub\r\n\r\n<\/code><\/pre>\n
\n<ComboBoxName>.RemoveItem <Index Number ><\/strong>
\n <\/p>\n\r\n\r\nSheet1.ComboBox1.RemoveItem 0\r\n\r\n<\/code><\/pre>\n
\n<\/p>\n How to Select First List Item as by Default: <\/h3>\n
\n
\n<ComboBoxName>.ListIndex= <Index Number ><\/strong>
\n
\nTo select an Item from the Drop Down you need to use the below Code
\n <\/p>\n\r\n\r\nSheet1.ComboBox1.ListIndex = 0\r\n\r\n<\/code><\/pre>\n
\nIn the Above example, First Item will be by default selected as I have used the Index Number as Zero (0). Similarly if you want to Select second or Third… give the Index Number of that Item.
\n
\n Want to Select Blank Option in Drop Down Box: <\/strong> To Select Blank Option from the Drop Down List, you should use Index Number as -1<\/strong>.
\n <\/p>\n\r\n\r\nSheet1.ComboBox1.ListIndex = -1\r\n\r\n<\/code><\/pre>\n
\n <\/p>\n\r\n\r\nSheet1.ComboBox1.ListIndex = Sheet1.ComboBox1.ListCount - 1\r\n\r\n<\/code><\/pre>\n
How to Get Selected Value of the Drop Down List: <\/h3>\n
\r\n\r\nMsgBox \"Selected Value is\" & ComboBox1.Value\r\n\r\n<\/code><\/pre>\n
How to Get Total Count of Items in a ComboBox<\/h3>\n
\nRefer the Below Code. It will give the the Total Number of Items in the ListBox1<\/strong>
\n<\/p>\n\r\nMsgBox (ComboBox1.ListCount)\r\n<\/code><\/pre>\n