Cancel Button to Stop Procedure

  • Thread starter Thread starter scadav
  • Start date Start date
S

scadav

I posted this question before, but I am afraid I still do not have a
solution. If anyone could help, it would be appreciated:

I am outputting some reports to Excel utilizing excel automation.
Sometimes the output is rather large and I would like to cancel it in the
middle of the output. How do I go about having a cancel button stop a loop
from occurring in a procedure?

Please keep in mind I am somewhat of a novice at access VBA.

Thanks.
 
The general idea is something like this, assuming that both the Excel
automation code and the Cancel button's Click event procedure are in the
same module:

'Declare a boolean variable that is accessible to both procedures:
Dim blCancel As Boolean

Private Sub cmdCancel_Click() 'Cancel button's click event
'Hoist the "Cancel" flag and sit back and wait
blCancel = True
End Sub

'Excel automation procedure
Sub XXX(blah blah)
...

'At beginning of procedure
blCancel = False

...

Do 'beginning of loop
'Do stuff in the loop
...
DoEvents 'allow other things to happen
'check the Cancel flag
If blCancel Then
'If anything is needed to exit the loop gracefully
'do it here
...
Else
'Prepare for next iteration
...
End If
Loop Until <normal end condition> Or blCancel

If blCancel Then
'Do any other tidying up needed
MsgBox "Operation Cancelled"
Else
MsgBox "Operation Completed"
End If

End Sub
 
The general idea is something like this, assuming that both the Excel
automation code and the Cancel button's Click event procedure are in
the same module:

'Declare a boolean variable that is accessible to both procedures:
Dim blCancel As Boolean

Private Sub cmdCancel_Click() 'Cancel button's click event
'Hoist the "Cancel" flag and sit back and wait
blCancel = True
End Sub

'Excel automation procedure
Sub XXX(blah blah)
...

'At beginning of procedure
blCancel = False

...

Do 'beginning of loop
'Do stuff in the loop
...
DoEvents 'allow other things to happen
'check the Cancel flag
If blCancel Then
'If anything is needed to exit the loop gracefully
'do it here
...
Else
'Prepare for next iteration
...
End If
Loop Until <normal end condition> Or blCancel

If blCancel Then
'Do any other tidying up needed
MsgBox "Operation Cancelled"
Else
MsgBox "Operation Completed"
End If

End Sub
Click to expand...

Thanks for your help, but I am still struggling to get this to work. My
major problem now is once the excel automation is running access won't
allow me to click any buttons. I tried a button right on the form,
popping up a modal form, playing with the onFocus settings, but still
nothing. Below is where I am looping through the excel automation and
printing to the screen.

Anyone got any ideas on how I can let the user hit a button while looping
through this....


Do Until rst.EOF
iFld = 0
lRecords = lRecords + 1
oExcelProgress.Visible = True
oExcelProgress.Value = "Exporting record #" & lRecords & " to " &
sOutput oExcelForm.Repaint

For iCol = cStartColumn To cStartColumn + (rst.Fields.Count - 1)
wks.Cells(iRow, iCol) = rst.Fields(iFld)

'Have special code for getting the skills this person is a part
of. If (sXLSTemplate =
"xlsTempEmployeeSearchResultsSummary.dll") And (iFld = 0) Then
wks.Cells(iRow, 32) =
ConcatenateSkillsAndRatings(rst.Fields(iFld))
End If

If InStr(1, rst.Fields(iFld).Name, "Date") > 0 Then
wks.Cells(iRow, iCol).NumberFormat = "mm/dd/yyyy"
End If

wks.Cells(iRow, iCol).WrapText = False
iFld = iFld + 1
Next

wks.Rows(iRow).EntireRow.AutoFit
iRow = iRow + 1
rst.MoveNext
Loop
 
Nowhere in your code do I see you call
DoEvents()

If you don't call that procedure within one of your loops then your process
doesn't yield execution so that the operating system can process other
events.


John Nurick's example shows this
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
Back
Top