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

Smithy02468

New Member
Joined
Aug 4, 2013
Messages
13

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.

Image


Image


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
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

John_w

MrExcel MVP
Joined
Oct 15, 2007
Messages
6,761
The select element has a change event so either one of these should work:
VBA Code:
    HTMLDoc.getElementById("sidebar_searchtype_select").FireEvent "onchange"
    While MyBrowser.Busy Or MyBrowser.readyState <> READYSTATE_COMPLETE: DoEvents: Wend
VBA Code:
    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
 
Solution

Smithy02468

New Member
Joined
Aug 4, 2013
Messages
13
The select element has a change event so either one of these should work:
VBA Code:
    HTMLDoc.getElementById("sidebar_searchtype_select").FireEvent "onchange"
    While MyBrowser.Busy Or MyBrowser.readyState <> READYSTATE_COMPLETE: DoEvents: Wend
VBA Code:
    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:
VBA Code:
    HTMLDoc.getElementById("sidebar_searchtype_select").FireEvent "onchange"
    While MyBrowser.Busy Or MyBrowser.readyState <> READYSTATE_COMPLETE: DoEvents: Wend
VBA Code:
    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

New Member
Joined
Aug 4, 2013
Messages
13
The select element has a change event so either one of these should work:
VBA Code:
    HTMLDoc.getElementById("sidebar_searchtype_select").FireEvent "onchange"
    While MyBrowser.Busy Or MyBrowser.readyState <> READYSTATE_COMPLETE: DoEvents: Wend
VBA Code:
    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

1614003976245.png


any ideas?

Regards
Colin
 

John_w

MrExcel MVP
Joined
Oct 15, 2007
Messages
6,761
Which line? If the select element isn't yet available, wait for it like this:
VBA Code:
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

New Member
Joined
Aug 4, 2013
Messages
13
Which line? If the select element isn't yet available, wait for it like this:
VBA Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,130,112
Messages
5,640,173
Members
417,129
Latest member
geekzilla

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top