Using excel vba to change the value of a dropdown

Question

I am writing an Excel macro to fill out a form on a website. I have written the code that populate the text boxes easily enough, and found code to chose radio boxes, but I am having problems with choosing info from dropdown menus.

Example 'Gender':

The combo box has three options:

Select / Male / Female

I've tried a few variations on this:

doc.getElementsByName("xs_r_gender").Item(0).Value="Male"

...but with no luck.

This is the web source code:

<td> <select name="xs_r_gender" id="xs_r_gender">
<option value="" selected>Select</option>
<option value="male">Male</option>
<option value="female">Female</option> </select></td>

Thanks.

Answer 1

Thanks Stack, works for me! My solution to operate an IE HTML combobox drop down turned out to be two parts.

Part 1 was to click the pull down, here's code:

Dim eUOM1 As MSHTML.HTMLHtmlElement
Set eUOM1 = ie.document.getElementsByTagName("input")(27).NextSibling
eUOM1.Focus
eUOM1.Click

Part 2 was to choose and click the value, like this (*actual element name changed):

Dim eUOM2 As MSHTML.HTMLHtmlElement
Set eUOM2 = ie.document.getElementsByName("[*PutNameHere]")(0)
eUOM2.Value = "EA"
eUOM2.Click

Here are references:refs

Answer 2

Use this in your code to call the function below.

xOffset = SetSelect(IE.Document.all.Item("shipToStateValue"), "Texas")
doc.getElementById("shipToStateValue").selectedindex = xOffset

Then use this for your function

Function SetSelect(xComboName, xComboValue) As Integer
    'Finds an option in a combobox and selects it.

    Dim x As Integer

    For x = 0 To xComboName.options.Length - 1
        If xComboName.options(x).Text = xComboValue Then
            xComboName.selectedindex = x
            Exit For
        End If
    Next x

    SetSelect = x

End Function

Answer 3

Try this code :

doc.getElementById("xs_r_gender").value = "Male"
doc.getElementById("xs_r_gender").FireEvent("onchange")

Answer 4

Try below code assuming doc = ie.document

doc.getElementById("xs_r_gender").value = "Male"

Answer 5

doc.getElementById("xs_r_gender").selectedindex=1
seems to do the trick. (Where 1 represents male)

Though it means I will need to do alot of lookups to determine what the value is for the items in my dropdown. (Easy enough for Sex, where there are only two options, but I have some comboboxes with up to 50 options). If anyone knows of a faster solution, that'd be great. In the meantime, Ill start doing up some tables!!!

thanks.

Answer 6

Function SetSelect(s, val) As Boolean

'Selects an item (val) from a combobox (s)
'Usage:
'If Not SetSelect(IE.Document.all.Item("tspan"), "Custom") Then
'something went wrong
'Else
'continue...
'End If

Dim x As Integer
Dim r As Boolean
r = False
For x = 0 To s.Options.Length - 1
If s.Options(x).Text = val Then
s.selectedIndex = x
r = True
Exit For
End If
Next x

SetSelect = r
End Function