Excel VBA to change the caption on an Excel command button

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:

It breaks on the name of the sub - did you compile the code first? And what in the world does the title have to do with the question? — Scott Holtzman
– Scott Holtzman, Commented Jun 22, 2016 at 20:54
Sorry forgot to change the title. See attached image for error msg. — Karen Schaefer
– Karen Schaefer, Commented Jun 22, 2016 at 21:16
Are you using Form Controls or ActiveX Controls? BTW, the error is because there is no such thing as CommandButton (when declaring variables). — Ralph
– Ralph, Commented Jun 22, 2016 at 21:20
I am using default Excel commandbutton I found the code else where and modified it. All I am trying to accomplish to change the caption on the button when the filter is applied and also when the filter is removed. — Karen Schaefer
– Karen Schaefer, Commented Jun 22, 2016 at 21:21

Ralph · Accepted Answer · 2016-06-22 21:29:05Z

1

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.

answered Jun 22, 2016 at 21:29

Ralph

9,4544 gold badges35 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Dick Kusleika · Accepted Answer · 2016-06-22 21:28:52Z

0

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

answered Jun 22, 2016 at 21:28

Dick Kusleika

33.3k4 gold badges56 silver badges81 bronze badges

2 Comments

Ralph Over a year ago

This works only for ActiveX controls. But it seems to me that Karen is using Form Controls.

Dick Kusleika Over a year ago

The CommandButton data type is ActiveX and the Shapes() accessor is more commonly used in Forms. I wonder where she copied that code from and what the heck they were using.

Collectives™ on Stack Overflow

Excel VBA to change the caption on an Excel command button

2 Answers 2

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related