Fill Combo Box in Excel with values from SELECT Dropdown using VBA

I have previously shared a similar post here that explained how you could extract or read data from an HTML element or a web page from your Excel worksheet.

The HTML Page with <select> Dropdown list

Here’s the web page (an HTML page), which has a <select> dropdown list element with some values in it. Open the web page, set focus on the dropdown list, right-click the mouse and choose the inspect option and you can see the values in it. See the image.

Also, see the id of the <select> element. We need this id of the element in our VBA code.

Add a ComboBox in your Excel Worksheet

Open an Excel file and save the file in .xlsm format (macro format). Add a Combobox and button control on your Excel sheet. Both are ActiveX controls. The buttons click event will call a procedure to extract data from the dropdown list and populate the ComboBox. You can write your code on any event.

Related Post: How to create a Cascading Combobox in Excel in few Simple steps

Add Microsoft HTML Object Library

We’ll first have to add a library reference to our application called Microsoft HTML Object Library. Using the library properties, we can open the web page or an HTML page on the Internet Explorer browser. In-addition, we can few HTML objects that will help extract the values from an element.

To do this, we’ll need to add a library called Microsoft HTML Object Library reference to your application.

From the top menu of your VBA editor, click Tools -> References…. In the References window, find and select Microsoft HTML Object Library and click OK.

Here’s the VBA code.

Option Explicit

Const sWebPage = "https://www.encodedna.com/jquery/demo/check-if-user-has-selected-value-from-select-dropdown-in-jquery.htm"

Private Sub CommandButton1_Click()
    fillComboBoxWithSelectDropDown
End Sub

Private Sub fillComboBoxWithSelectDropDown()
    ' Create Internet Explorer object.
    Dim IE As Object
    Set IE = CreateObject("InternetExplorer.Application")
    IE.Visible = False          ' Keep IE hidden.
    
    IE.Navigate sWebPage
    
    ' Wait till IE is fully loaded.
    While IE.ReadyState <> 4
        DoEvents
    Wend
    
    Dim oHDoc As HTMLDocument     ' Create document object.
    Set oHDoc = IE.Document
    
    Dim oHEle As HTMLSelectElement                  ' Create HTML element object.
    Set oHEle = oHDoc.getElementById("selBooks")    ' Get the SELECT element's reference using its ID.
    
    Dim iCnt As Integer
    
    With oHEle
        If .Length > 0 Then     ' Check the SELECT dropdown has any value (options).
        
            cmbBooks.Clear      ' Clear the combox box.
        
            For iCnt = 1 To .Length - 1
                cmbBooks.AddItem .Item(iCnt).Value
            Next iCnt
        End If
    End With
    
    ' Clean up.
    IE.Quit
    Set IE = Nothing
    Set oHEle = Nothing
    Set oHDoc = Nothing
End Sub

The constant in the beginning of the code, has the URL of the page where I have the <select> element. You can change this value with another URL.

Const sWebPage = https://www.encodedna.com/jquery/demo/check-if-user-has-selected-value-from-select-dropdown-in-jquery.htm 

After creating the Internet Explorer object, I have set its Visible property as False.

IE.Visible = False ' Keep IE hidden.

It will remain hidden and we’ll quietly extract the element’s value. Once the operation is over, we’ll close the browser and clear all its instances.

To extract values from my <select> element, I have created an object of property HTMLSelectElement and assigned the id of the <select> element to the object.

Dim oHEle As HTMLSelectElement                  ' Create HTML element object.
Set oHEle = oHDoc.getElementById("selBooks")    ' Get the element reference using its ID.

Finally, we’ll loop through each value in the dropdown list and fill our ComboBox with the values.

Do you know you can easily create a Cascaded ComboBox in Excel? Read this post.

Well, that’s it. Thank for reading :-).

← PreviousNext →