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.

 

[John_w]

I used the "Loop While... disabled", because while the button is enabled it doesn't say enabled anywhere in the html code.

My enabled/disabled strings are just examples, and may not apply to your HTML. What is the button's HTML before and after it is enabled/active?

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

It shouldn't matter. JavaScript is a web page programming language which can generate/update HTML on web pages, whilst HTML is the static web page markup.

 

[Peter h]

My enabled/disabled strings are just examples, and may not apply to your HTML. What is the button's HTML before and after it is enabled/active?

Oh ok. I wasn't familiar enough with your code to notice anything would need to be changed to apply to my specific situation. Sorry, newb status. lol. Here is the HTML code for the button:

<INPUT *******="if (typeof(Page_ClientValidate) == 'function') Page_ClientValidate(); " tabIndex=26 disabled id=btnRemove ******************** style="HEIGHT: 29px; WIDTH: 101px; POSITION: absolute; LEFT: 760px; Z-INDEX: 129; TOP: 376px; BACKGROUND-COLOR: red" type=submit value="Remove Assays" name=btnRemove>

When I "Inspect Element" instead of saying "disabled id=btnRemove", it says "Disabled="disabled"", but for some reason that doesn't copy when I try to copy and paste the code.

Does this help? I was going to just do a couple of screenshots, but I don't have access to a photo host site from work.

 

[John_w]

You have to put a space after every < when posting HTML code, otherwise the forum tries to render the HTML.

Without seeing the HTML after the button is enabled, it's hard to know exactly what to try. But if,as it seems, the string "disabled" is always part of the button's HTML, then you could try checking for the disabled attribute - which is in this part of the HTML "tabIndex=26 disabled id=btnRemove".

    Dim button As HTMLButtonElement
    Set button = HTMLdoc.getElementById("btnRemove")
    Do
        DoEvents
    Loop Until button.getAttribute("disabled") Is Nothing 'loop until the button's disabled attribute doesn't exist

assuming the disabled attribute is removed when the button is active.

 

[Peter h]

Ok, I'm sorry I'm making this so difficult. Thank you so much for your help and patience with me. The loop still didn't work. Here is the HTML for when it's disabled:

< INPUT *******="if (typeof(Page_ClientValidate) == 'function') Page_ClientValidate(); " tabIndex=26 disabled id=btnRemove ******************** style="HEIGHT: 29px; WIDTH: 101px; POSITION: absolute; LEFT: 760px; Z-INDEX: 129; TOP: 376px; BACKGROUND-COLOR: red" type=submit value="Remove Assays" name=btnRemove >

And here it is when the button is enabled:

< INPUT *******="if (typeof(Page_ClientValidate) == 'function') Page_ClientValidate(); " tabIndex=26 id=btnRemove ******************** style="HEIGHT: 29px; WIDTH: 101px; POSITION: absolute; LEFT: 760px; Z-INDEX: 129; TOP: 376px; BACKGROUND-COLOR: red" type=submit value="Remove Assays" name=btnRemove >

The only difference is the "disabled" before the ID in the first one.

 

[Peter h]

Right now, as it is, I have my userform set up so that I can select from a combobox any data that has been previously been entered. When I make a selection it populates the textboxes in the user form with the data that was entered. The textboxes are populated with data that is in the spreadsheet (which matches the data entered into the webform). I then can make my changes and modify the data or remove the sample.

Would it be better if when I selected a sample from the combobox the Textboxes are filled with the data off the webform, rather than the spreadsheet? And then from there, when I click on my "Remove" button the data is already loaded on the webpage from the combobox_select event? Then the button_click event would only have to handle clicking the button on the webform, right? Would that make it easier at all?