CODEWITHSUNDEEP
excelvba
Karen Schaefer
Karen Schaefer

Reputation: 109

Excel VBA to change the caption on an Excel command button

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: enter image description here

Upvotes: 0

Views: 9387

Answers (2)

Ralph
Ralph

Reputation: 9444

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.

Upvotes: 1

Dick Kusleika
Dick Kusleika

Reputation: 33175

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

Upvotes: 0

Related Questions