pause macro and show message box to click on it to continue

Hi All,

I have some code that runs, but needs to be paused for a moment and then continue when a user clicks a button on message box that appears.
so it could be like below:

code runs
code comes to a pause point and shows message box
user clicks message box ok button when he finish
code continues

This will be part of a loop.

I was googeling with no luck. the only thing I found was the below that gives me an error when clicking on it (is like getting a second continue with code button)

RetVal = MsgBox("Program Paused - Click OK to continue", vbOKOnly + vbInformation)

any idea how to solve this?

Greetings.

Hi,
I did not, I'm new to all of this and have no clue.
Let's say I have below loop. I would like it to pause before the next i. How should I declare it and put the message box in the correct place?

Dim i As Integer

For i = 1 To 6
    Cells(i, 1).Value = 100
Next i

Greetings.

Hi Jmac1947,

That is the idea. I need to stop the loop so it will not run untill I say so.

Greetings.

Hi Leon V,

The message box really stops it, when I click OK, I get the message box from excel: Code execution hs been interrupted > continute, end, debug, help.
This is kind of annoying that it brings me to the message box from excel and need to press again continue.
Any solution for not getting this one?

Greetings.

Hi all,

I created my loop, but it misses something.
It should take the cell in column 10, and then when looping the next, the row below the previous one.

Please see my code and tell me where I go wrong, please ;-)

For i = 2 To 6

Call IE.document.getElementById("searchvalue").SetAttribute("value", 10, i) 'ActiveCell.Value)
Do While IE.readyState <> READYSTATE_COMPLETE Or IE.Busy: DoEvents: Loop




Set toolsForm = doc.forms(0)

Set SignInButton = toolsForm.elements("p_submit")
    SignInButton.Click
Application.DisplayAlerts = False
MsgBox "Program Paused - Click OK to continue"
On Error Resume Next
Application.DisplayAlerts = False

Next i

Hi,

there was no break in the code, everything runs fine. I wanted to pause the code on that point. It kind of works with the double pressing a button, but the loop does not work the way it should.
How can I tell the loop to go to the next row in column 10? (my expirience with loops is minimal).

Greetings.

Hi,

I found some code for the loop, but get strange results as it does copy nothing from column 10. the value that it has to copy is: CJ 22945/2014 (so I don't know if it has to do with the way the text is)
can some one look at the below code and tell me where I go wrong?

Greetings.

FinalRow = Cells(Rows.Count, 10).End(xlUp).Row
For i = 2 To FinalRow ' it will start at row 2

Call IE.document.getElementById("searchvalue").SetAttribute("value", i) 'ActiveCell.Value)
Do While IE.readyState <> READYSTATE_COMPLETE Or IE.Busy: DoEvents: Loop

Set toolsForm = doc.forms(0)

Set SignInButton = toolsForm.elements("p_submit")
    SignInButton.Click
MsgBox "Program Paused - Click OK to continue"
Next i

Hi,

It seems that the macro paste in random numbers, not the one from the current cell that has value of i in the loop.

Hi all,

I guess I think I know what is wrong. I changed the loop code to below. Now it will pass "2" to the field in the website. It turns out that the value of i is 2. I need the value of the cell that the loop is in.
How can I change that part to make it work? please ;-)

   
 Set doc = IE.document
    
    'Get the only form on the page
    
    Set toolsForm = doc.forms(0)
    
    Set Textvalue = toolsForm.elements("searchValue")
    Textvalue.Value = i
Do While IE.readyState <> READYSTATE_COMPLETE Or IE.Busy: DoEvents: Loop

Set toolsForm = doc.forms(0)

Set SignInButton = toolsForm.elements("p_submit")
    SignInButton.Click
MsgBox "Program Paused - Click OK to continue"
Next i

Hi all,
The solution from Solus did work out ;-) I just did not come up with the idea that I needed to pass the column value too ;-)
So Solus, THANKS A LOT ;-)

Now that the macro runs nice, I still have a little thing I would lilke to get rid of. The Internet explorer save as dialog box opens every time and I need to click 2 times to get rid of it (once for the chose folder and second time for saving it).
Is there a way to block this while the macro runs? it really slows things down :-(

All of you BIG THANKS ;-)

Greetings.

Hi Solus,

If I now could get rid of the error when I come to the message box and it does not give the stupid error message, would be great + the evil IE save as dialog box

Here is the full code:

Sub Tester() 'PART 1


    Dim LastRow As Long
    Dim i As Integer
    
    Dim IE As Object
    Dim objElement As Object
    Dim objCollection As Object
    Dim ieDoc As Object
    Dim ieObj As Object
    Dim doc As HTMLDocument
    Dim LoginForm As HTMLFormElement
    Dim UserNameInputBox As HTMLInputElement
    Dim PasswordInputBox As HTMLInputElement
    Dim SignInButton As HTMLInputButtonElement
    Dim HTMLelement As IHTMLElement
    Dim qt As QueryTable
    
'The final row finder
FinalRow = Cells(Rows.Count, 10).End(xlUp).Row
    
    
    
Set IE = CreateObject("InternetExplorer.Application")

IE.Visible = True
IE.Navigate "http://" 'make sure you've logged into the page
Do While IE.readyState <> READYSTATE_COMPLETE Or IE.Busy: DoEvents: Loop

Set doc = IE.document
ActiveSheet.EnableCalculation = False
ActiveSheet.EnableCalculation = True
Do While IE.readyState <> READYSTATE_COMPLETE Or IE.Busy: DoEvents: Loop

Call IE.document.getElementById("username").SetAttribute("value", ActiveSheet.Range("b2").Value)

Do While IE.readyState <> READYSTATE_COMPLETE Or IE.Busy: DoEvents: Loop
Call IE.document.getElementById("password").SetAttribute("value", ActiveSheet.Range("a2").Value)

Do While IE.readyState <> READYSTATE_COMPLETE Or IE.Busy: DoEvents: Loop
IE.document.getElementById("login").Click  ' click the "search" button

Do While IE.readyState <> READYSTATE_COMPLETE Or IE.Busy: DoEvents: Loop
IE.Navigate "http://my website"
Application.SendKeys "{TAB 2}", True

Do While IE.readyState <> READYSTATE_COMPLETE Or IE.Busy: DoEvents: Loop

IE.Navigate "httpmy website&"
Do While IE.readyState <> READYSTATE_COMPLETE Or IE.Busy: DoEvents: Loop


Application.SendKeys "%{Left}", True
Do While IE.readyState <> READYSTATE_COMPLETE Or IE.Busy: DoEvents: Loop

Application.SendKeys "{TAB}", True
Do While IE.readyState <> READYSTATE_COMPLETE Or IE.Busy: DoEvents: Loop

'>>>Here should come the loop<<<

FinalRow = Cells(Rows.Count, 10).End(xlUp).Row
For i = 2 To FinalRow ' it will start at row 2
Do While IE.readyState <> READYSTATE_COMPLETE Or IE.Busy: DoEvents: Loop

'Call IE.document.getElementById("searchvalue").SetAttribute(i, "value")
    Set doc = IE.document
    
    'Get the only form on the page
    
    Set toolsForm = doc.forms(0)
    
    Set TextValue = toolsForm.elements("searchValue")
    TextValue.Value = Cells(i, 10).Value
Do While IE.readyState <> READYSTATE_COMPLETE Or IE.Busy: DoEvents: Loop

Set toolsForm = doc.forms(0)

Set SignInButton = toolsForm.elements("p_submit")
    SignInButton.Click
MsgBox "Program Paused - Click OK to continue"
Next i

End Sub

Hi all,

I found the below link:
http://www.mrexcel.com/forum/excel-q...cations-3.html

And wanted to know if someone can help me in understanding which part I need to take from the code to stop the annoying save as dialog from windows. The file name that is downloading has unique name. All I need is that it uses the folder name that is in the cell to the left of the one the loop is current on. So if it happens to be that the loop is in row 25, then next to it is the folder name and that should be used to save the file in.

Greetings.