How to pause Excel VBA and wait for user interaction before loop

Question

I have a VERY basic script I am using with an excel spreadsheet to populate a form. It inserts data into the fields then waits for me to click the submit button on each page. Then it waits for the page to load and fills in the next set of fields. I click submit, next page loads, etc. Finally, on the last two pages I have to do some manual input that I can't do with the spreadsheet. Code thus far is shown below.

My question is, at the end of what I have there now, how can I make the system wait for me to fill out that last page, then once I submit it realize that it has been submitted and loop back to the beginning, incrementing the row on the spreadsheet so that we can start over and do the whole thing again for the next student?

As you will probably be able to tell I am not a programmer, just a music teacher who does not savor the idea of filling out these forms manually for all 200 of my students and got the majority of the code you see from a tutorial.

    Function FillInternetForm()
  Dim IE As Object
  Set IE = CreateObject("InternetExplorer.Application")
'create new instance of IE. use reference to return current open IE if
'you want to use open IE window. Easiest way I know of is via title bar.
  IE.Navigate "https://account.makemusic.com/Account/Create/?ReturnUrl=/OpenId/VerifyGradebookRequest"
'go to web page listed inside quotes
  IE.Visible = True
  While IE.busy
    DoEvents  'wait until IE is done loading page.
  Wend
      IE.Document.All("BirthMonth").Value = "1"
          IE.Document.All("BirthYear").Value = "2000"
  IE.Document.All("Email").Value = ThisWorkbook.Sheets("queryRNstudents").Range("f2")
    IE.Document.All("Password").Value = ThisWorkbook.Sheets("queryRNstudents").Range("e2")
        IE.Document.All("PasswordConfirm").Value = ThisWorkbook.Sheets("queryRNstudents").Range("e2")
            IE.Document.All("Country").Value = "USA"
    IE.Document.All("responseButtonsDiv").Click

newHour = Hour(Now())
newMinute = Minute(Now())
newSecond = Second(Now()) + 3
waitTime = TimeSerial(newHour, newMinute, newSecond)
Application.Wait waitTime

      IE.Document.All("FirstName").Value = ThisWorkbook.Sheets("queryRNstudents").Range("a2")
    IE.Document.All("LastName").Value = ThisWorkbook.Sheets("queryRNstudents").Range("b2")
    IE.Document.All("Address1").Value = "123 Nowhere St"
    IE.Document.All("City").Value = "Des Moines"
    IE.Document.All("StateProvince").Value = "IA"
    IE.Document.All("ZipPostalCode").Value = "50318"



End Function

Also, don't use all, as its possible you could have 2 controls or more with the same ID, use getElementsByID....TagName......ClassName and use the xth array element from it, unless the plan is to populate all if there are more than 1 — Nathan_Sav
– Nathan_Sav, Commented Dec 13, 2016 at 16:21

Nathan_Sav · Accepted Answer · 2016-12-13 16:08:27Z

1

I would use the events of the IE, more specifically the form, something like this, using MSHTML Controls library.

Private WithEvents IEForm As MSHTML.HTMLFormElement

Public Sub InternetExplorerTest()

Dim ie As SHDocVw.InternetExplorer
Dim doc As MSHTML.HTMLDocument

Set ie = New SHDocVw.InternetExplorer
ie.Visible = 1
ie.navigate "http://stackoverflow.com/questions/tagged/vba"

While ie.readyState <> READYSTATE_COMPLETE Or ie.Busy
    DoEvents
Wend

Set doc = ie.document
Set IEForm = doc.forms(0)

End Sub

Private Function IEForm_onsubmit() As Boolean
    MsgBox "Form Submitted"
End Function

edited Dec 13, 2016 at 16:08

answered Dec 13, 2016 at 16:00

Nathan_Sav

8,5762 gold badges18 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to pause Excel VBA and wait for user interaction before loop

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related