One More question

[lidojuagon]

Ok I'm almost done with my project, but it's been so long since I've used VB, so I appologize if these are easy questions. I've searched this formum and internet, but I don't think I'm describing my problem correct.

I sent up a script to run a loop and print a bunch of reports. I want to have a cancel button in the loop so that the user can cancel in the middle of the process, as it could take up to 30 mintues or so. It's in VB for excel. I'm thinking set up a form with a cancel button but I don't know how to incoroprate that button to the print sub. Can anyone help

Sub refreshtesting()



Dim EntityArray
Dim X As Integer
Dim iResponse As Integer
Dim iResponse2 As Integer

If iResponse = vbYes Then ' They Clicked YES!

EntityArray = Array("E02202", "E00750")

Do Until X = UBound(EntityArray) + 1

I would like to add the cancel button here.

Range("B9").Select
ActiveCell.FormulaR1C1 = EntityArray(X)

Application.ScreenUpdating = False
Application.EnableEvents = False
Application.DisplayStatusBar = True
Application.ScreenUpdating = False
Application.Wait Now + TimeValue("00:00:03")

Application.EnableEvents = True
Run "MNU_ETOOLS_REFRESH"
Run "MNU_ETOOLS_EXPAND"

Application.DisplayStatusBar = False
Application.ScreenUpdating = True


X = X + 1

Loop


Else
'If they clicked no on not being sure
End If

End Sub

[smartcanix]

maybe you can try something like this:

Dim bStopNow As Boolean

Private Sub Form_Load()
   bStopNow = False
End Sub

Private Sub Command1_Click()
   Dim dStart As Date

   dStart = Now
   Do Until (DateDiff("s", dStart, Now) > 60) Or (bStopNow)
      Debug.Print Now
      DoEvents
   Loop
   bStopNow = False
End Sub

Private Sub Command2_Click()
   bStopNow = True
End Sub

Good luck

[Hack]

It's in VB for excel.

Excel VBA question moved to Office Development

[opus]

I suppose you have a UserFom to start that code.
In this case create a global boolean var "StopScript" in the general section.
Put this line into the _click event of the Cancle button:

StopScript=True

and change the bold line in your code to read:

If StopScript= True then Exit Sub

You might consider to hide the Userform in that event also!