Interrupt code with a userform cancel button

[kalvy]

I am attempting to have a macro run within Excel that builds a PowerPoint presentation, and meanwhile have a userform Cancel button displayed while the macro is running. If the Cancel button is clicked, the code should stop running (can finish formatting the slide it's currently on but don't get back in loop to start a next one.) Currently, I am unable to click on the Cancel button in the userform while the code is executing. The code also moves between Excel and PowerPoint so active windows change and sometimes the userform is hidden by the PowerPoint file- regardless, it's not working while it is showing. Everything else is in this code is functioning fine and the Userform even says "Creating 1/3 Slides", "Creating 2/3 Slides", etc.

I've seen a couple other threads on cancel buttons, etc. with some useful hints that I am trying, but they vary slightly. Like: http://www.mrexcel.com/forum/excel-...p-code-running-programming-cancel-button.html


Private Sub SOV_Cancel_Click()
CancelSOV = True
SOV_Form.Hide
End Sub


Private Sub UserForm_Activate()

Dim DoneSOV As Boolean
Dim ItemsCount1 As Integer
Dim ItemsCount2 As Integer
Dim ItemsCount3 As Integer
Dim SlideCount As Integer
Dim pptLayout As CustomLayout

CancelSOV = False
DoneSOV = False

txtSlidesNumber.Text = SlidesNum
SlideCount = 1

Do While Not CancelSOV And Not DoneSOV
DoEvents
ItemsCount1 = 1
While SlicerArray(1).Items(ItemsCount1) <> ""
Call ChangeSlicer(1, ItemsCount1)

ItemsCount2 = 1
While SlicerArray(2).Items(ItemsCount2) <> ""
Call ChangeSlicer(2, ItemsCount2)

ItemsCount3 = 1
While SlicerArray(3).Items(ItemsCount3) <> ""
Call ChangeSlicer(3, ItemsCount3)

If SlideCount = 1 Then
If CancelSOV Then Exit Sub
txtSlidesNumber.Text = SlideCount & "/" & SlidesNum
Call Setup
If Not VerifyFiles Then Exit Sub
If Not SetSource Then Exit Sub
Set SOVSlide = OpenSlide(PowerPointBase, 1)
If ChartNum = 1 Then
Call PieSlide
Else
Call BarSlide
End If
SlideCount = SlideCount + 1
Call CleanUp
Else
If CancelSOV Then Exit Sub
txtSlidesNumber.Text = SlideCount & "/" & SlidesNum
Call Setup
If Not SetSource Then Exit Sub
Set pptLayout = OpenPres.Slides(1).CustomLayout
Set SOVSlide = OpenPres.Slides.AddSlide(SlideCount, pptLayout)
If ChartNum = 1 Then
Call PieSlide
Else
Call BarSlide
End If
SlideCount = SlideCount + 1
Call CleanUp
End If

If SlideCount > SlidesNum Then DoneSOV = True

ItemsCount3 = ItemsCount3 + 1
Wend
ItemsCount2 = ItemsCount2 + 1
Wend
ItemsCount1 = ItemsCount1 + 1
Wend

Loop

SOV_Form.Hide
End Sub

 

[sijpie]

I partly answered this in the "original" thread which was split by a moderator from the first thread.

http://www.mrexcel.com/forum/excel-questions/810312-cancel-button-userform.html

See if that helps you solve your issue. Else post a reply here with further update.

 

[kalvy]

I partly answered this in the "original" thread which was split by a moderator from the first thread.

http://www.mrexcel.com/forum/excel-questions/810312-cancel-button-userform.html

See if that helps you solve your issue. Else post a reply here with further update.

Thank you, sijpie!!! I put DoEvents all over my code and that helped! It was only working when I had Excel and PowerPoint on different monitors and I fixed that by taking out any PPT activations. I still have a busy cursor while the code is executing, the button sometimes doesn't compress when clicked, and some minor things, but this is big progress! Thanks!