Need some help with this DoEvents loop. Please.

[Peter h]

Hey guys. I have recently built userforms that submit data entry into worksheets, but also into a webform for work. It all works perfectly for submitting data, but I've decided to make my userforms able to not only submit data, but to also remove any data that needs to be removed. So, as of right now, if I want to remove or modify any data that has already been entered into our webform, I have to go to the url, select the data to be removed or modified from a ListBox, and then click a button to remove or modify. By doing that, I wasn't changing any of the data that was also saved in the workbook, without going in manually and changing it myself. The whole point of creating the userform was to eliminate steps, and I'm not able to do that by going back and forth between excel and the webform. Here is the code I have right now for my "Remove" button in my Userform:

Private Sub CommandButton2_Click()
'Retrieve selected item value in combobox3 change event_______________
Dim TheValue As String
TheValue = efsform.ComboBox3.Text

'Find the corresponding row______________________________
Dim TheSearch As Object
Dim TheRange As Range
Set TheRange = Sheet2.Range(Sheet2.Cells(2, 3), Sheet2.Cells(Sheet2.UsedRange.Rows.Count, 1))
Set TheSearch = TheRange.Find(What:=TheValue, After:=TheRange.Cells(TheRange.Cells.Count), LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)

    Sheet2.Cells(TheSearch.Row, 2).EntireRow.Delete
    
'Manipulate the Webform____________________________________
Dim IE As InternetExplorerMedium
Dim targetURL As String

targetURL = "http://miap33wsapx16/asoma/asomaentryform.aspx"
Set IE = New InternetExplorerMedium

    IE.Visible = True ' Set to true to watch what's happening
    IE.Navigate targetURL

    Do Until IE.ReadyState = READYSTATE_COMPLETE
        DoEvents
    Loop

     IE.Document.getElementById("ddlSelection").selectedIndex = 1
     IE.Document.getElementById("ddlSelection").FireEvent ("onchange")
     
     Do
        DoEvents
    Loop While IE.Document.getElementById("Sample_Arrival_Time") Is Nothing

'__Entered Sample Selection__________________________________________________
Dim selectElement As HTMLSelectElement
Dim optionIndex As Integer
    
Set selectElement = IE.Document.getElementById("ListBox1")
optionIndex = FindSelectOptionIndex(selectElement, ComboBox3.Value)
    If optionIndex >= 0 Then
        selectElement.selectedIndex = optionIndex
    Else
        MsgBox ComboBox3.Value & " Selected sample not found. "
    End If
IE.Document.getElementById("ListBox1").FireEvent ("onchange")

Application.Wait (Now + #12:00:02 AM#)

IE.Document.getElementById("btnRemove").Click

Unload efsform

End Sub

Private Function FindSelectOptionIndex(selectElement As HTMLSelectElement, findOptionText As String) As Integer

    Dim i As Integer
    
    FindSelectOptionIndex = -1
    i = 0
    While i < selectElement.Options.Length And FindSelectOptionIndex = -1
        Debug.Print i, selectElement.Item(i).Value & " >" & selectElement.Item(i).Text & "<"
        If LCase(selectElement.Item(i).Text) = LCase(findOptionText) Then FindSelectOptionIndex = i
        i = i + 1
    Wend

    
End Function

Right now the code works, but it's because I put in the 'Application.Wait' to allow the data to load after selecting the entered data from the listbox. I feel like using the Wait function isn't the best way of going about this, because if the server ever lags for some reason then it will throw off the rest of the code. I've tried many different DoEvent loops to try to get it to click the button after the data has loaded, but can't seem to get any to work. I've tried:

Do
    DoEvents
Loop While IE.Document.getElementById("Copper").innerText = ""

just as a guess to see if I can get it to loop while the entry field is blank, but I get an error doing that.

Anyone have any suggestions? If I'm not explaining myself or the situation very good, feel free to ask. Thanks for any help.

 

[mole999]

could you not mark the data to not be displayed, i.e a value in an additional field, that when the form refreshes doesn't show it

 

[Peter h]

I'm sorry, maybe I'm tired, but I'm not understanding your question.

 

[mole999]

you can edit what already exists?

can you add an additional field that you can see

and if it duplicate add a marker in there

then when the form refreshes, those with a marker are not displayed ?

 

[John_w]

Along the same lines as the previous wait loop, replace the Application.Wait with:

Do
    DoEvents
Loop While IE.Document.getElementById("btnRemove") Is Nothing
IE.Document.getElementById("btnRemove").Click

 

[Peter h]

Thanks for the reply John. I tried that, with no luck. I think the reason it doesn't work is because the button is there, it's just not active until you've selected an entered sample. So, how the Web form looks is, like before, you select the sample type from the drop down list, the form updates with the appropriate data fields. When it updates there is a listbox that populates with the previous samples that were entered. There are buttons, "accept assay", "remove", and "modify". The remove and modify buttons are there, just not active until a sample is selected from the listbox. I've looked for some element in the form that isn't there until a sample is selected from the listbox, to use for the loop, but the only thing that changes when selecting one is that the data entry fields populate with the data that was entered and the remove and modify buttons become clickable. That's why I tried to do the loop until there is data in one of the data fields, but with no luck.

 

[John_w]

Is there something in the button's HTML which says whether it is enabled or disabled? Usually the class or some other attribute changes. Try something like this which looks for "enabled" or "disabled" (case-insensitive) anywhere in the button's HTML.

    Dim button As HTMLButtonElement
    Set button = IE.Document.getElementById("btnRemove")
    Do
        DoEvents
    Loop Until InStr(1, button.innerHTML, "enabled", vbTextCompare) > 0    'loop until innerHTML contains "enabled"
    'Or
    'Loop While InStr(1, button.innerHTML, "disabled", vbTextCompare) > 0  'loop while innerHTML contains "disabled"

 

[Peter h]

Yes, it says in the code that it is disabled, and then when it becomes enabled that part of the code is gone. I'll try your loop right now, and let you know how it goes. I figured there had to be a way for the code to check the state of an element, but didn't know the correct syntax.

 

[Peter h]

Ok, I just tried it, and it still isn't clicking the button. I'm not sure what's going on here. I used the "Loop While... disabled", because while the button is enabled it doesn't say enabled anywhere in the html code.

 

[Peter h]

Does it matter that the language for the webform is JavaScript? Is JavaScript and HTML the same?