Need some help with this DoEvents loop. Please.

Peter h

Active Member
Joined
Dec 8, 2015
Messages
417
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:

Code:
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:

Code:
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.
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

mole999

Well-known Member
Joined
Oct 23, 2004
Messages
10,524
Office Version
  1. 2019
  2. 2016
  3. 2013
Platform
  1. Windows
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

Active Member
Joined
Dec 8, 2015
Messages
417
I'm sorry, maybe I'm tired, but I'm not understanding your question.
 

mole999

Well-known Member
Joined
Oct 23, 2004
Messages
10,524
Office Version
  1. 2019
  2. 2016
  3. 2013
Platform
  1. Windows
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

MrExcel MVP
Joined
Oct 15, 2007
Messages
7,702
Along the same lines as the previous wait loop, replace the Application.Wait with:
Code:
Do
    DoEvents
Loop While IE.Document.getElementById("btnRemove") Is Nothing
IE.Document.getElementById("btnRemove").Click
 

Peter h

Active Member
Joined
Dec 8, 2015
Messages
417
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

MrExcel MVP
Joined
Oct 15, 2007
Messages
7,702
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.

Code:
    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

Active Member
Joined
Dec 8, 2015
Messages
417
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

Active Member
Joined
Dec 8, 2015
Messages
417
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

Active Member
Joined
Dec 8, 2015
Messages
417
Does it matter that the language for the webform is JavaScript? Is JavaScript and HTML the same?
 

Forum statistics

Threads
1,185,996
Messages
5,955,222
Members
438,187
Latest member
DT1962

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