Drawing ComboBox from IE for use in excel VBA

vba_newbie2013

New Member
Joined
Jun 18, 2013
Messages
29
Hey, new here to the forums and i just want to say how helpful this place has been throughout my coding process. I hadnt even looked at a line of VB until a couple of weeks ago, but now i've got a good foundation thanks to websites like this.

To my question: I was wondering if there was anyway to retrieve values from a combobox on a webpage (For my case there are two values in the combobox.) I know how to manipulate the webpage to choose the values in the combobox and so on but i want my user to be able to see the options they have from my userform. This might be a little vague so please dont hesitate to ask for clarification. Thanks for any assitance anyone can provide.
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
It's probably possible, depends how the combobox has been populated/created, which depends on the page.

Do you have any URL in mind?
 
Upvote 0
Good to see you again Norie! And yes actually: Individual and Family Health Insurance

I need to grab the information from the county comboBox (which only shows up after the user has entered their zip and pressed enter.), and i guess there could be multiple different numbers of counties in a zip code. I was thinking of trying something along the lines of this:

county1 = IE.document.getElementById("census_county").Value(0)

for every county, but seems to not be working with a 'Object doe not support this property or method' error. Here's the code if that helps at all:


Code:
 Dim IE As InternetExplorer
Dim myZip As Long
Dim siteZip As Object
Dim URL As String
Dim gender As Object
Dim objInputs As Object
Dim county1 As Object

Sub Macro_1()

myZip = "29020"
Set IE = CreateObject("InternetExplorer.Application")
IE.Visible = True
URL = "[URL="http://www.ehealthinsurance.com/individual-family-health-insurance"]Individual and Family Health Insurance[/URL]"
IE.navigate (URL)

Do
DoEvents
Loop Until IE.readyState = 4

Set siteZip = IE.document.getElementById("census_zipCode")
siteZip.Value = myZip

'Set gender = IE.document.getElementById("census_primary_gender")
'gender.Value = "MALE"

Set objInputs = IE.document.getElementsByTagName("input")
For Each ele In objInputs

    If ele.Title Like "Get Individual & Family Health Insurance Quotes" Then
        ele.Click
    End If
Next


'Wait till page has loaded
Application.Wait Now + TimeValue("00:00:02")
Set county1 = IE.document.getElementById("census_county").Value(0)
Debug.Print county1

End Sub
 
Upvote 0
A combobox only has one value and that's the one you chose.

So you can't use Value, and you definitely can't use it on 'census-county' because that's a label.:)

The combobox is called 'census.county'.

You can use this to get the values from the list.
Code:
    Set county1 = IE.document.forms(1).all("census.county")
    For i = 0 To county1.Length - 1
        Debug.Print county1(i).innerText
    Next i
 
Upvote 0
Thanks Norie that worked perfectly. Now i just have a question on some of my code thats throwing a rather strange error. I've got two different environments/projects; one where i'm actually building a project around a userform and another where i test the the code to make sure it works. The following code is working in the testing environment but when i test it in my userform project the tool throws a 'Object does not support this property or method'. I've stared at it a good while and cant seem to figure out what is going on that would make this the case. Now with that out of the way here's the code for the test environment and userform:

Test Environment:

Code:
Dim IE As InternetExplorer
Dim myZip As Long
Dim siteZip As Object
Dim URL As String
Dim gender As Object
Dim objInputs As Object
Dim countyOptions As Object
Dim countyChoice As String
Dim choCounty As Object
 
Sub Macro_1()
myZip = "29020"
Set IE = CreateObject("InternetExplorer.Application")
IE.Visible = True
URL = "[URL="http://www.ehealthinsurance.com/individual-family-health-insurance"]Individual and Family Health Insurance[/URL]"
IE.navigate (URL)
Do
DoEvents
Loop Until IE.readyState = 4

Set siteZip = IE.document.getElementById("census_zipCode")
siteZip.Value = myZip

Set gender = IE.document.getElementById("census_primary_gender")
gender.Value = "MALE"

Set objInputs = IE.document.getElementsByTagName("input")
For Each ele In objInputs
    If ele.Title Like "Get Individual & Family Health Insurance Quotes" Then
        ele.Click
    End If
Next

'Wait till page has loaded
Application.Wait Now + TimeValue("00:00:02")

'County Selection
'Enter County
countySuc = True
countyChoice = "Kershaw"
Set countyOptions = IE.document.forms(1).all("census.county")
countyChoice = UCase(countyChoice)
    For i = 0 To countyOptions.Length - 1
        If countyChoice = countyOptions(i).innerText Then
            countyOptions.Value = countyChoice
            countySuc = True
            Exit For
        Else
            countySuc = False
        End If
    Next i
If countySuc = False Then
    MsgBox ("The county you entered is incorrect. Your options are: " & countyOptions(1).innerText & " and " & countyOptions(2).innerText)
    Exit Sub

End If
End Sub

Userform Environment( only the actual function. Theres near a thousand lines of code in the project itself):

Code:
'Enter County
countySuc = True
countyChoice = county
Set countyOptions = IE.document.forms(1).all("census.county")
countyChoice = UCase(countyChoice)
    For i = 0 To countyOptions.Length - 1 'This is the line that is throwing the error 
        If countyChoice = countyOptions(i).innerText Then
            countyOptions.Value = countyChoice
            countySuc = True
            Exit For
        Else
            countySuc = False
        End If
    Next i
If countySuc = False Then
    MsgBox ("The county you entered is incorrect. Your options are: " & countyOptions(1).innerText & " and " & countyOptions(2).innerText)
    Exit Sub
End If

userform Dimensions:

Code:
Dim IE As InternetExplorer
Dim myZip As Long
Dim siteZip As Object
Dim URL As String
Dim moreChildren As Object
Dim people As Integer
Dim DOBday As Integer
Dim priDay As Object
Dim priMon As Object
Dim priYea As Object
Dim secDay As Object
Dim secMon As Object
Dim secYea As Object
Dim chiDay As Object
Dim chiMon As Object
Dim chiYea As Object
Dim dateCheck As Boolean
Dim i As Integer
Dim counter As Integer
Dim objInputs As Object
Dim countyChoice As String
Dim countyOptions As Object
Dim countySuc As Boolean

I can honestly say i havent been this frustrated with a segment of code in sometime:mad:. Any help would be greatly appreciated. And please dont hesitate to ask any questions. Thanks!
 
Upvote 0
Any clue as to which line of code the error occurs on?

By the way, do you have a lot of repetive code in the userform module?
 
Upvote 0
Norie i actually figured out what the problem was. I forgot to put a wait in there for the webpage to load with the potential county options.:oops: Works perfectly now.

But now i'm running into another problem. Once again not sure if this is allowed but i acutally started another thread to address the problem. Essentially once i enter all this info and press 'Go' i cant do it again because the webpage saves all that information. Here's the thread: http://www.mrexcel.com/forum/excel-...tboxes-webpage-visual-basic-applications.html
 
Upvote 0
Why don't you just navigate back to the orignal URL?
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,237
Members
448,555
Latest member
RobertJones1986

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
Back
Top