Excel vba code to select option from drop down box in website

[Smithy02468]

Good day​

I have created the VBA code process in excel for accessing a website via username and password which works upto a point where it opens an option dropdown box that is defaulted to address. the other option in the drop down box is the one i need it to select and start entering the grid coordinates which can be obtained form the spreadsheet. i am stuck on the code to make it choose "Co-ordinates so i can progress to make it take the coordinates from the spreadsheet.

Sub Virgin_Click() 'Module8_Virgin

'Call BrowsetoVirgin
Dim MyHTML_Element As IHTMLElement
Dim Myurl As String
Dim HTMLInput As MSHTML.IHTMLElement
Dim HTMLDoc As MSHTML.HTMLDocument

Myurl = "*************"
' irj/portal"
Set MyBrowser = New InternetExplorer
MyBrowser.Silent = True
MyBrowser.navigate Myurl
MyBrowser.Visible = True
Do
Loop Until MyBrowser.readyState = READYSTATE_COMPLETE
Set HTMLDoc = MyBrowser.document
HTMLDoc.all.ctl00_ContentPlaceHolder1_txtUserName.Value = "Username" 'Enter your email id here
' HTMLDoc.all.ctl00_ContentPlaceHolder1_txtPassword.Value = "Password" 'Enter password
HTMLDoc.all.ctl00_ContentPlaceHolder1_txtPassword.Value = ThisWorkbook.Sheets("Stats").Range("E6").Value 'Enter password

'Log in button press
Set HTMLInput = HTMLDoc.getElementById("ctl00_ContentPlaceHolder1_LoginButton")
HTMLInput.Click

which comes up with the form on the website below. The dropdown on clicking offers two options, Address or Co-ordinates. I want to choose Coordinates. to enter the Eastings and Northings.







and i want to choose by selecting it in the dropdown. on clicking the dropdown and selecting the Coordinates option by a click it changes the form to the coordinates version. i have managed to code to change the value of the dropdown box but it is stuck here.

'<select tabindex="1" id="sidebar_searchtype_select" onchange="SearchTypeRequest(null,'ctl00_ContentPlaceHolder1_WaterMap','sidebar',
'this.options[this.selectedIndex].value);"><option selected="selected" value="address">Address</option><option value="location">Co-ordinates</option></select>

HTMLDoc.getElementById("sidebar_searchtype_select").Click
HTMLDoc.getElementById("sidebar_searchtype_select").Value = "location"
HTMLDoc.getElementById("sidebar_searchtype_select").Click

Can anyone help with this?
Thanks in advance for any help given.
Colin

 

[John_w]

The select element has a change event so either one of these should work:

    HTMLDoc.getElementById("sidebar_searchtype_select").FireEvent "onchange"
    While MyBrowser.Busy Or MyBrowser.readyState <> READYSTATE_COMPLETE: DoEvents: Wend

    Dim changeEvent As Object
    Set changeEvent = HTMLDoc.createEvent("HTMLEvents")
    changeEvent.initEvent "change", True, False
    HTMLDoc.getElementById("sidebar_searchtype_select").dispatchEvent changeEvent
    While MyBrowser.Busy Or MyBrowser.readyState <> READYSTATE_COMPLETE: DoEvents: Wend

 

[Smithy02468]

The select element has a change event so either one of these should work:

    HTMLDoc.getElementById("sidebar_searchtype_select").FireEvent "onchange"
    While MyBrowser.Busy Or MyBrowser.readyState <> READYSTATE_COMPLETE: DoEvents: Wend

    Dim changeEvent As Object
    Set changeEvent = HTMLDoc.createEvent("HTMLEvents")
    changeEvent.initEvent "change", True, False
    HTMLDoc.getElementById("sidebar_searchtype_select").dispatchEvent changeEvent
    While MyBrowser.Busy Or MyBrowser.readyState <> READYSTATE_COMPLETE: DoEvents: Wend

The select element has a change event so either one of these should work:

    HTMLDoc.getElementById("sidebar_searchtype_select").FireEvent "onchange"
    While MyBrowser.Busy Or MyBrowser.readyState <> READYSTATE_COMPLETE: DoEvents: Wend

    Dim changeEvent As Object
    Set changeEvent = HTMLDoc.createEvent("HTMLEvents")
    changeEvent.initEvent "change", True, False
    HTMLDoc.getElementById("sidebar_searchtype_select").dispatchEvent changeEvent
    While MyBrowser.Busy Or MyBrowser.readyState <> READYSTATE_COMPLETE: DoEvents: Wend

Hi John, The second one worked a treat, onward and upwards i can progress with the code for inputting the grid coordinates from the website, your solution is much appreciated.

Regards
Colin

 

[Smithy02468]

The select element has a change event so either one of these should work:

    HTMLDoc.getElementById("sidebar_searchtype_select").FireEvent "onchange"
    While MyBrowser.Busy Or MyBrowser.readyState <> READYSTATE_COMPLETE: DoEvents: Wend

    Dim changeEvent As Object
    Set changeEvent = HTMLDoc.createEvent("HTMLEvents")
    changeEvent.initEvent "change", True, False
    HTMLDoc.getElementById("sidebar_searchtype_select").dispatchEvent changeEvent
    While MyBrowser.Busy Or MyBrowser.readyState <> READYSTATE_COMPLETE: DoEvents: Wend

Hi John,

Just looking to progress on with the code you gave me, when i ran it i stepped through the code and as i mentioned it worked, however on running the code from the play button it throws up an error

any ideas?

Regards
Colin

 

[John_w]

Which line? If the select element isn't yet available, wait for it like this:

Dim SelectElement As HTMLSelectElement
Do 
    Set SelectElement = HTMLDoc.getElementById("sidebar_searchtype_select")
    DoEvents
Loop While SelectElement Is Nothing

Then continue with the rest of the code, but using the SelectElement variable instead of HTMLDoc.getElementById("sidebar_searchtype_select")

 

[Smithy02468]

Which line? If the select element isn't yet available, wait for it like this:

Dim SelectElement As HTMLSelectElement
Do
    Set SelectElement = HTMLDoc.getElementById("sidebar_searchtype_select")
    DoEvents
Loop While SelectElement Is Nothing

Then continue with the rest of the code, but using the SelectElement variable instead of HTMLDoc.getElementById("sidebar_searchtype_select")

Thanks John, so much to learn with all the different options/ commands
Regards
Colin