Can you please define your problem in more detail?
What will be the starting cell? A1? A2?
And how far down will we going? Or do we just stop at the last populated cell in column A?
I apologize for the lack of detail. Currently Ive managed to make this kinda work using an event handler and several call outs. Ill be starting in A2 and cycling down to the last populated row.
I've pasted my various codes below and theres a lot to take in but currently it kinda works. The first sub opens IE and logs me into the Database, turns on the event handler and calls out to the next sub. The second sub simply checks to see if the cell is populated with text and if so calls out to the third sub. Sub three which is my biggest pain takes the data from my spreadsheet and enters it into the web page, saves it and moves to the next cell where the event handler takes over. The event handler calls out to Sub two causing it to run the entire process again while skipping the login step from sub one. The problem I'm trying to solve is that Sub three which enters the data into the web data base opens a new instance of IE each time it performs a task and then closes it each time its done. I would prefer to not do this as it introduces way to many opportunities for errors and hang ups. I would prefer to have sub one contain a loop that would perform the task while redirecting urls instead of having to use event handlers and some convoluted way of performing this task. My second larger issue is that sub three uses sendkeys and its only mildly reliable at best. Either way I'm basically attempting to loop all the code in sub three and that's my biggest hang up at the moment. I've also added a screen shot of the spread sheet that I'm using to do all of this.
Sub LOGIN()
Dim ie As InternetExplorer
Set ie = New InternetExplorer
Application.EnableEvents = True
ie.Visible = True
ie.Navigate2 " URL Goes Here to log me into the web based database"
Do While ie.Busy: DoEvents: Loop
Do Until ie.ReadyState = READYSTATE_COMPLETE: DoEvents: Loop
Application.Wait (Now + TimeValue("0:00:02"))
ie.FullScreen = True
ie.Document.all("TextBox_UserName").Value = "My User Name "
ie.Document.all("TextBox_LoginPassword").Value = "My Pass Word"
ie.Document.all("Button_Login").Click
Do While ie.Busy: DoEvents: Loop
Do Until ie.ReadyState = READYSTATE_COMPLETE: DoEvents: Loop
Application.Wait (Now + TimeValue("0:00:02"))
Call CellChecks
End Sub
Sub CellChecks()
Dim rCell As Range
Set rCell = ActiveCell
For Each Cell In rCell
If Len(rCell.Formula) = 0 Then
MsgBox "List Complete"
Else
Call Cycle_Assignment
End If
Next
End Sub
Sub Cycle_Assignment()
Dim ie As InternetExplorer
Dim i As Variant
Dim Enumber As String
Enumber = ActiveCell.Offset(0, 3).Value
Set ie = New InternetExplorer
On Error Resume Next
ie.Visible = True
ie.FullScreen = True
ie.Navigate2 "This opens a New IE and directs to a different page after I log In"
Do While ie.Busy: DoEvents: Loop
Do Until ie.ReadyState = READYSTATE_COMPLETE: DoEvents: Loop
ie.Document.all("TextBox_TagNum").Value = ActiveCell.Value
ie.Document.all("ddlFacility").Value = "TH"
Application.Wait (Now + TimeValue("0:00:05"))
Application.SendKeys "{TAB 7}", True
Application.Wait (Now + TimeValue("0:00:05"))
Application.SendKeys "~"
Application.Wait (Now + TimeValue("0:00:05"))
ie.Document.all("GridView_CyclesFound_DropDownList_NewTech_0").Value = Enumber
ie.Document.all("GridView_CyclesFound_DropDownList_NewTech_1").Value = Enumber
ie.Document.all("GridView_CyclesFound_DropDownList_NewTech_2").Value = Enumber
ie.Document.all("GridView_CyclesFound_DropDownList_NewTech_3").Value = Enumber
Application.Wait (Now + TimeValue("0:00:05"))
ie.Document.all("Button_UpdateAll").Click
Application.Wait (Now + TimeValue("0:00:05"))
ie.Quit
ActiveCell.Offset(1, 0).Select
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim LastPopulatedRow As Long
LastPopulatedRow = Range("A" & Rows.Count).End(xlUp).Row
If Intersect(Target, Range("A2: " & "A" & LastPopulatedRow)) Is Nothing Then
Application.EnableEvents = False
MsgBox "List Complete"
Else
CellChecks
End If
End Sub
