How to Delay A Loop On Comand Or With Code

[NYFashionToGo]

I have some code that I been using and It works fine. however I am looking for a better way to make this work. As you will see below, The code builds a URL based on values of cells.

I use this to cyle through online invoices to invoice people. I have this on time delay sequence.(shortened for example) So I am allowed a minute to process it and press submit before the next page loads in same IE window (without opening a new page). If the phone rings, Im lost and start over. If I take a second too long, I am lost and have to figure out the one I missed.

If I do this without the loop it opens up a Million IE windows.

If there is a way to Pause the code (with code) and then resume it again ( Right where I left off) On a click? With Some more code? Is This Doable? Or even to close The existing IE window as I press a next button would work, That would be without the loop. So One closes and then a new one opens...


I am not sure about how to go about this. If anyone can give me some insight and Ideas how to make this work better, I would appreciate it.. Thank You!




Option Explicit

Public Declare Function ShowWindow _
Lib "user32" ( _
ByVal hwnd As Long, _
ByVal nCmdShow As Long) _
As Long

Public Declare Function SetForegroundWindow _
Lib "user32" ( _
ByVal hwnd As Long) _
As Long

Public Const SW_MAXIMIZE As Long = 3& 'Show window Maximised
Sub OpenIEInvoicing()

'For This Example Put Value of 705 in Cell A2, 706 In cell A3, 707 in Cell A4

Range("A2").Select

Dim objIe As Object
Dim strFilePath As String

Set objIe = CreateObject("internetexplorer.application")

Do
strFilePath = "

http://www.tek-tips.com/threadminder.cfm?pid="

& (ActiveCell.Value)

With objIe
.Navigate strFilePath
.offline = True
.Visible = True
ShowWindow objIe.hwnd, SW_MAXIMIZE
SetForegroundWindow objIe.hwnd


End With

' Pause Here
' There is more code here but does not effect this loop structure
'time delay is shortened to get point across

Application.Wait (Now + TimeValue("0:00:15"))
ActiveCell.Offset(1, 0).Select
Loop Until IsEmpty(ActiveCell.Value)

End Sub






Thanks

 

[Bong]

Others will tell you that using .select is inefficient and should be avoided (there's a FAQ to that effect). They're right, but that's beside the point. You might try something like this:

...
[red]moreTime=6
while moreTime=6
  Application.Wait (Now + TimeValue("0:00:15"))
  moreTime = msgbox("more time?",4,"test")
loop[/red]
ActiveCell.Offset(1, 0).Select
Loop Until IsEmpty(ActiveCell.Value)

_________________
Bob Rashkin