Excel VBA to change the caption on an Excel comman

Question

I am looking for Excel VBA to change the caption on an Excel command button on the fly. The default caption should be "Show Difference" and should change to Show All when the filter is applied.

This is what I have so far.

Sub ShowDifference()
Dim cmdButton As CommandButton

'Breaks Here

Set cmdButton = ActiveSheet.Shapes("cmdShowDif")


If cmdButton.Caption = "Show Difference" Then
    ActiveSheet.ListObjects("qryDifference").Range.AutoFilter Field:=4, _
    Criteria1:=Array("<>0.00"), Operator:=xlAnd
    cmdButton.Caption = "Show All"
Else
    ActiveSheet.ListObjects("qryDifference").Range.AutoFilter Field:=4
    cmdButton.Caption = "Show Difference"
End If

End Sub

It breaks on the name of the sub. Why?

Error:

Answer 1

Go to the Developer tab and click Design Mode. Now select your CommandButton. The name of the button will appear in the name box - to the left of the formula bar. Change this line

Set cmdButton = ActiveSheet.Shapes("cmdShowDif")

to

Set cmdButton = ActiveSheet.OLEObjects("cmdShowDif").Object

But that uses the correct name instead of cmdShowDif (or change the name in the name box to cmdShowDif

Answer 2

Here is the working code:

Sub ShowDifference()

Dim cmdButton As Button

Set cmdButton = ActiveSheet.Buttons("cmdShowDif")

If cmdButton.Caption = "Show Difference" Then
    cmdButton.Caption = "Show All"
Else
    cmdButton.Caption = "Show Difference"
End If

End Sub

Alternatively, you can also use the following code:

Sub ShowDifference()

Dim cmdButton As Button

For Each cmdButton In ActiveSheet.Buttons
    If cmdButton.Name = "cmdShowDif" Then
        If cmdButton.Caption = "Show Difference" Then
            cmdButton.Caption = "Show All"
        Else
            cmdButton.Caption = "Show Difference"
        End If
    Else
        Debug.Print cmdButton.Name & " is not the one... moving to next button..."
    End If
Next cmdButton

End Sub

Let me know if you have any questions.