Cancel button on userform

kalvy

New Member
Joined
Oct 7, 2014
Messages
4
I am attempting to have a macro run within Excel that builds a PowerPoint presentation and meanwhile have a userform cancel button that is displayed while the macro is running, that the user can press to stop the macro. For the macro to run while the cancel button is showing, I have the code within UserForm_Activate. The cancel button is linked to the following:

Code:
Private Sub SOV_Cancel_Click()
CancelSOV = True
SOV_Form.Hide
End Sub
And the code within UserForm_Activate constantly checks the value of CancelSOV and is supposed to Exit Sub when it = True.


-Main issue: As the code within UserForm_Activate is running and specifically building a powerpoint presentation, I am unable to click on the cancel button. I do have Application.ScreenUpdating = False while code runs, if you think that's the issue.
-Since moving my code to the UserForm_Activate, one of my counters is not functioning properly when the macro is run a second time- as if it's saving the value from the previous time. I have even attempted to set it to 0 and then reset it, but somewhere in the code it's going back to the counter from the last time the macro was run?
 
Last edited by a moderator:

Some videos you may like

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

sijpie

Well-known Member
Joined
Nov 1, 2008
Messages
3,684
I am attempting to have a macro run within Excel that builds a PowerPoint presentation and meanwhile have a userform cancel button that is displayed while the macro is running, that the user can press to stop the macro. <snip>.... code it's going back to the counter from the last time the macro was run?

As this is a different issue form the original post, more about interrupting running code with a cancel button, please post this as a new post.

Once you have done that send me a private message with the link and I will address the issue. It is pretty straightforward (I think)
 

sijpie

Well-known Member
Joined
Nov 1, 2008
Messages
3,684
I have a simple userform with two buttons, Button1 is the OK, Button2 is the Cancel

Pressing Button1 runs the stuff that takes a long time. Button2 sets a variable bStop to true.

Code:
Option Explicit

Dim bStop As Boolean


Private Sub CommandButton1_Click()
    Dim lL As Long
    
    [a1] = lL
    Application.ScreenUpdating = False
    bStop = False
    
    For lL = 1 To 10000
        [a1] = lL
        DoEvents
        If bStop Then Exit For
    Next lL
    Application.ScreenUpdating = True
    
End Sub


Private Sub CommandButton2_Click()
    bStop = True
End Sub

note how in the loop DoEvents is called. This tells excel to break for a millisec in order to allow Windows to process events. That way your keypress of the cancel button will be registered.

If your presentation compiling software does not use a loop then add DoEvents throughout the code (for instance where you ask it to check your variable)
 

Watch MrExcel Video

Forum statistics

Threads
1,109,205
Messages
5,527,399
Members
409,760
Latest member
zeeshansyed

This Week's Hot Topics

Top