0

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

Community
  • 1
  • 1
Karen Schaefer
  • 99
  • 2
  • 3
  • 9
  • What is the error? – Matt Cremeens Jun 22 '16 at 20:51
  • *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 Jun 22 '16 at 20:54
  • Sorry forgot to change the title. See attached image for error msg. – Karen Schaefer Jun 22 '16 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 Jun 22 '16 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 Jun 22 '16 at 21:21
  • Neither form controls nor activeX controls are "default". Well, it seems you don't know. So, let's assume that you are using form controls. So, try to change `Dim cmdButton As CommandButton` to `Dim cmdButton As Button`. Here you can read-up on ActiveX Controls vs Form Controls: http://stackoverflow.com/questions/15455179/what-is-the-difference-between-form-controls-and-activex-control-in-excel-20 – Ralph Jun 22 '16 at 21:23
  • I changed to Dim cmdButton As Button and still get The item with the specified name wasn't found (Set cmdButton = ActiveSheet.Shapes("cmdShowDif") – Karen Schaefer Jun 22 '16 at 21:26

2 Answers2

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.

Ralph
  • 9,284
  • 4
  • 32
  • 42
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

Dick Kusleika
  • 32,673
  • 4
  • 52
  • 73
  • This works only for ActiveX controls. But it seems to me that Karen is using Form Controls. – Ralph Jun 22 '16 at 21:35
  • 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. – Dick Kusleika Jun 23 '16 at 13:27