(Solved) – VBA error when triggering “change” event on IE input field


I’m trying to use VBA to access an internal web page using Internet Explorer, open a search window, enter search parameters and run the search. My code will click all of the buttons, find all of the text boxes and enter the parameters. But, it does not recognize any of the parameters entered using “.value” (i.e., objElement.value = “1234567”). I have written the code based on what I have read in this forum and a number of others but, obviously, I’m still missing something. When it tries to trigger the “change” event, I receive either an Error 438 – Object Doesn’t Support this Property or Method, or an Error 5 – Invalid Procedure Call or Argument. The error depends on whether I use dispatchEvent or FireEvent and whether I use the collection or the element within the collection as the object. My guess is the problem lies with my declarations but, I tried a few different ones, and still get the errors.

Unfortunately, this is accessing an internal site so you won’t be able to run the code. I’m hoping someone can just take a look and point me in the right direction.

Dim EPA As String
Dim EPANumb As Integer
Dim EPAList() As String
Dim PrjCnt As Long
Dim PrjList As String
Dim WS As Worksheet
Dim IE As InternetExplorerMedium
Dim URL As String
Dim HWNDSrc As Long
Dim objCollection As Object
Dim objElement As Object

Dim objEvent As Object
Dim oHtml As HTMLDocument
Dim HTMLtags As IHTMLElementCollection

'Pull list of project numbers and enter into array
fn = ThisWorkbook.Name
EPANumb = WorksheetFunction.CountA(Worksheets("Instructions").Range("B:B"))
EPANumb = EPANumb - 2
ReDim EPAList(EPANumb)
For PrjCnt = 0 To EPANumb
    If EPANumb > 0 Then
        EPAList(PrjCnt) = "'" & Worksheets("Instructions").Cells((PrjCnt   2), 2).Value & "'"
        EPAList(PrjCnt) = Worksheets("Instructions").Cells((PrjCnt   2), 2).Value
    End If

PrjList = Join(EPAList, ",")

'Open IE and navigate to URL
Set IE = New InternetExplorerMedium

IE.Visible = True 'only if you want to see what is happening
URL = "http://anysite/SnakeEyes/grid.html"
IE.navigate URL
'need If statement in case user needs to do sign on.

' Statusbar let's user know website is loading
Application.StatusBar = URL & " is loading. Please wait..."

'IE ReadyState = 4 signifies the webpage has loaded (the first loop is set to avoid inadvertently skipping over the second loop)
Do While IE.Busy = True Or IE.ReadyState <> 4: DoEvents: Loop
Application.Wait (Now   TimeValue("0:00:05"))

'Webpage Loaded
Application.StatusBar = URL & " loaded"
Set oHtml = IE.document

'SetForegroundWindow HWNDScr

'Open search box and reset search parameters
Set objEvent = oHtml.createEvent("HTMLEvents")
objEvent.initEvent "change", True, False ' **** NEW ****

Set HTMLtags = oHtml.getElementsByTagName("select")
For i = 0 To HTMLtags.Length - 1
    If HTMLtags(i).className = "selectopts" Then
        If EPANumb > 0 Then
            HTMLtags(i).Value = "in"
            HTMLtags(i).Value = "eq"
        End If
'        HTMLtags.dispatchEvent objEvent ' **** NEW ****
        Exit For
    End If
Next i

Set objEvent = oHtml.createEvent("HTMLEvents")
objEvent.initEvent "change", True, False ' **** NEW ****

Set HTMLtags = oHtml.getElementsByTagName("input")
For i = 1 To HTMLtags.Length - 1
    If HTMLtags(i).ID > "jqg" And HTMLtags(i).ID < "jqh" Then
        HTMLtags(i).Value = PrjList
        Exit For
    End If
Next i

HTMLtags(i).dispatchEvent objEvent ' **** NEW ****
'HTMLtags(i).FireEvent objEvent ' **** NEW **** DispatchEvent gives an error 438.
'FireEvent gives an error 5 if using the (i); error 438 without it.

Leave a Reply

Your email address will not be published. Required fields are marked *