Command Button Caption and Execution in VBA

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

Private Sub CommandButton1_Click() With CommandButton1 If .Caption = "Execute" Then 'if button caption is Execute .Caption = "Running" 'change caption to Running For i = 1 To 10000 'looping from 1 to 10000 Cells(i, 1) = i 'write i value to cells Label1 = "Processed :" & "(" & i & "/10000)" 'update progress to label1 Next End If .Caption = "Execute" 'after finish chage caption back to Execute End With End Sub

But, when we execute, we cant see the progress. So, we need to add DoEvents function to update the process on userform.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17

Private Sub CommandButton1_Click() With CommandButton1 If .Caption = "Execute" Then 'if button caption is Execute .Caption = "Running" 'change caption to Running DoEvents 'wait until event finish (Caption become Running) For i = 1 To 10000 'looping from 1 to 10000 Cells(i, 1) = i 'write i value to cells Label1 = "Processed :" & "(" & i & "/10000)" 'update progress to label1 DoEvents 'wait until event finish (write i to cell 'and process update on label1 Next End If .Caption = "Execute" 'after finish chage caption back to Execute End With End Sub

Now we add Stop option:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22

Dim FlagStop As Boolean Private Sub CommandButton1_Click() With CommandButton1 If .Caption = "Execute" Then 'if button caption is Execute .Caption = "Stop" 'change caption to Running DoEvents 'wait until event finish (Caption become Running) FlagStop = False For i = 1 To 10000 'looping from 1 to 10000 Cells(i, 1) = i 'write i value to cells Label1 = "Processed :" & "(" & i & "/10000)" 'update progress to label1 DoEvents 'wait until event finish (write i to cell 'and process update on label1 If FlagStop Then Exit For Next Else If MsgBox("Do you want to stop the process?", vbYesNo) = vbYes Then FlagStop = True End If .Caption = "Execute" 'after finish chage caption back to Execute End With End Sub

Download File