Using excel vba to change dropdown menu value on website

Question

I am writing an Excel macro to fill out a form on a website. I wrote code that fills the text fields quite easily, and found code to select the radio blocks, but I have problems choosing information from the drop-down menus.

Example 'Gender':

The combo box has three options:

Select / Male / Female 

I tried several variations of this:

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

... but without 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> 

Thank you

Answer 1

doc.getElementById("xs_r_gender").selectedindex=1
seems to be doing the trick. (Where 1 stands for men)

Although this means that I will have to look a lot to determine what value is for the items in my drop-down list. (Easy enough for Sex, where there are only two options, but I have some combinations with up to 50 options). If anyone knows of a faster solution, that would be great. In the meantime, I will start making some tables !!!

thanks.

Answer 2

Try entering code assuming doc = ie.document

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

Answer 3

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 4

Thanks, Stack, it works for me! My decision to use a dropdown in HTML HTML came in two parts.

Part 1 was supposed to click on the dropdown code, here is the code:

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

Part 2 was to select and click the value, like this (* the real name of the element has changed):

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

Here are the links: links

Answer 5

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

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

or

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

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 

Answer 7

Try this code:

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

Answer 8

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 version (in both cases).

If a dropbox needs to trigger an event in order to know your choice, it is likely that it will be an "onchange" event. You can run it like this:

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

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

This should cover all bases.

Answer 9

Copy from here to the 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