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

How to Know SHIFT/CTRL/ALT was pressed in VBA

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

Private Sub CommandButton1_MouseUp(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single) Dim str As String Select Case Shift Case 1 str = "SHIFT was pressed." Case 2 str = "CTRL was pressed." Case 3 str = "SHIFT + CTRL was pressed." Case 4 str = "ALT was pressed." Case 5 str = "ALT + SHIFT was pressed." Case 6 str = "ALT + CTRL was pressed." Case 7 str = "ALT + SHIFT + CTRL was pressed." End Select Me.Label1 = str End Sub

Download File

How to Know Right/Left/Middle Click on Command Button in VBA

a. First add a userform to a Workbook (right click VBE(Visual Basic Editor) > Insert > Userform)
b. Add a command button and a label

c. Right Click Command Button, and then click View Code
d. Then choose, Mouseup event

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

Private Sub CommandButton1_MouseUp(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single) Dim str As String Select Case Button Case 1 str = "The left button was pressed." Case 2 str = "The right button was pressed." Case 4 str = "The middle button was pressed." Case Else str = "The unknown button was pressed." End Select Me.Label1 = str End Sub

The settings for Button are:

Download File