Prompt user before continuing loop

Question

I'm creating a VB macro to loops through cells and copy and paste the values into notepad. What I'd like to do is prompt the user and ask if they'd like to continue before doing the next iteration.

Sub LoopTest()

Dim rng As Range, cell As Range
Set rng = Range("A1:A2")
For Each cell In rng

cell.Copy

'Worksheets("Sheet1").Range("A" + j).Copy
Shell "C:\Windows\system32\notepad.exe", vbNormalFocus
SendKeys "^V"
DoEvents
SendKeys "{ENTER}", True
Next cell


End Sub

Comintern · Accepted Answer · 2015-11-12 03:03:19Z

5

Try this:

Sub LoopTest()

    Dim rng As Range, cell As Range
    Set rng = Range("A1:A2")
    For Each cell In rng

        cell.Copy

        'Worksheets("Sheet1").Range("A" + j).Copy
        Shell "C:\Windows\system32\notepad.exe", vbNormalFocus
        SendKeys "^V"
        DoEvents
        SendKeys "{ENTER}", True

        If MsgBox("Continue?", vbYesNo, "Confirm") = vbNo Then

            Exit For

        End If

    Next cell


End Sub

edited Nov 12, 2015 at 3:03

Comintern

22.3k6 gold badges36 silver badges83 bronze badges

answered Nov 12, 2015 at 2:18

MGP

2,6732 gold badges22 silver badges35 bronze badges

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

2 Comments

PASUMPON V N Over a year ago

I have taken few line from you. Is it fine right for you?

MGP Over a year ago

Yes of course. The code doesn't belong to me and the purpose of this website is to provide free help. So feel free to use it.

PASUMPON V N · Accepted Answer · 2015-11-12 02:45:25Z

0

I have used FileSystemObject method to write a code

Sub test1()

    Dim fso As Object
    Set fso = CreateObject("Scripting.FileSystemObject")

    Dim strpath As String

    strpath = "" '~~~~> path

    Dim oFile As Object

    Set oFile = fso.CreateTextFile(strpath)

    Dim rng As Range, cell As Range
    Set rng = Range("A1:A2")
    For Each cell In rng
        If MsgBox("Continue?", vbYesNo, "Confirm") = vbYes Then

            oFile.WriteLine cell.Value
        Else
            Exit For
        End If

    Next cell

    oFile.Close

    Set fso = Nothing

    Set oFile = Nothing

    End Sub

answered Nov 12, 2015 at 2:45

PASUMPON V N

1,1862 gold badges10 silver badges17 bronze badges

Collectives™ on Stack Overflow

Prompt user before continuing loop

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related