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

You can try the querySelector method of document to apply a CSS selector of option tag with attribute value = 'male':

doc.querySelector("option[value='male']").Click

or

doc.querySelector("option[value='male']").Selected = True

Answer 2

Copy from Here till last line:

Sub Filldata()

Set objShell = CreateObject("Shell.Application")

IE_count = objShell.Windows.Count

For X = 0 To (IE_count - 1)

On Error Resume Next    ' sometimes more web pages are counted than are open

my_url = objShell.Windows(X).document.Location

my_title = objShell.Windows(X).document.Title

   If my_title Like "***Write your page name***" Then

    Set IE = objShell.Windows(X)

        Exit For

    Else

        End If

    Next

With IE.document.forms("***write your form name***")

' Assuming you r picking values from MS Excel Sheet1 cell A2

i=sheet1.range("A2").value 

.all("xs_r_gender").Item(i).Selected = True

End with

End sub

Answer 3

You can do something like this:

doc.getElementsByName("xs_r_gender").Item(1).Selected=True

or

doc.getElementById("xs_r_gender").selectedindex = 1

Where 1 is the male option (in both cases).

If the dropbox needs to fire some event in order to aknowledge your choice, it is likely that it will be the "onchange" event. You can fire it like so:

doc.getElementById("xs_r_gender").FireEvent("onchange")

If you ever want to be able to select an option based on the option's text you can use the function given by Lansman (here) . Based on the same answer, if you want to call the option by it's value property (instead of the text, you can just change the line If xComboName.Options(x).Text = xComboValue Then to If xComboName.Options(x).value = xComboValue Then).

This should cover all bases.