Change a Command Button Caption at certain conditions.

[samabert]

Hello,

In the attachment, I have 2 command buttons.
The first button caption “ Set Filter” change when you click on it and at that moment the second button (Print) will be unhide so you can use this button as well.
So far this works, but if you forget to set the command button “Undo Filter” back. If you go to another sheet or if you close this worksheet is it possible that the button will be set back automatically to“Set Filter” and the Print button will be return to hide.

Thank you in advance.
Marc

[Tommy]

On sheet1 in the code pane place:

Private Sub Worksheet_Deactivate()     
cmb_Filter.Caption = "Set Filter"
CommandButton1.Visible = False
End Sub

[samabert]

   Private Sub cmb_Filter_Click()
    If cmb_Filter.Caption = "Set Filter" Then
        cmb_Filter.Caption = "Undo Filter"
        CommandButton1.Visible = True
 ActiveSheet.ListObjects("Tabel5").Range.AutoFilter Field:=3, Criteria1:= _
        "<>"
    Else
        cmb_Filter.Caption = "Set Filter"
        
        CommandButton1.Visible = False
  ActiveSheet.ListObjects("Tabel5").Range.AutoFilter Field:=3
    End If
End Sub

 Private Sub Worksheet_Deactivate()
cmb_Filter.Caption = "Set Filter"
    CommandButton1.Visible = False
 ActiveSheet.AutoFilterMode = False
End Sub

The caption of the button changed , so far ok. But the filter selection made in the table, does not return to any filter selection, as if you use the button “Undo filter”.
The Undo filter code under “Private Sub cmb_Filter_Click()” with the button works fine, but how do you return No filter in the “Private Sub Worksheet Deactivate()”??

Hope you can help
Marc

[Tommy]

samabert,

I am unsure as to what you are asking. There was no mention of returning anything. It was just change the caption and hide a commandbutton.
So I am unsure what you are saying when you say "Return no filter". I can help further, I just want to be clear on what you want.

[SamT]

Auto-Click the button on Deactivate.

Private Sub Worksheet_Deactivate() 
    If cmb_Filter.Caption = "Undo Filter"  Then  cmb_Filter_Click
End Sub

[samabert]

To SamT, thank you for your response, but with your code I get the same result as with Tommy’s .


To Tommy, It is my fault that the explanation about this problem is not so clear, sorry.
Up to now I have: when I click on the button Set Filter, the button name changes to Undo Filter and the print button gets visible.
At the same time when I click the button Set Filter, I set a filter on the Table5
ActiveSheet.ListObjects("Tabel5").Range.AutoFilterField:=3, Criteria1:= _ "<>"



When I click on the same button, but now with the name Undo Filter, the button name changes to Set filter and the Table5 filter goes back to normal table size. The print button goes back invisible. So far this part works fine and you will find it under: Private Sub cmb_Filter_Click()

Private Sub cmb_Filter_Click()
If cmb_Filter.Caption = "Set Filter" Then
      cmb_Filter.Caption = "Undo Filter"
      CommandButton1.Visible = True
     ActiveSheet.ListObjects("Tabel5").Range.AutoFilter Field:=3, Criteria1:= _
      "<>"
    Else
    cmb_Filter.Caption = "Set Filter"
    CommandButton1.Visible = False
    ActiveSheet.ListObjects("Tabel5").Range.AutoFilter Field:=3
End If
End Sub

The code that you give me, changes the name of the button from Undo filter to Set filter and the print button becomes invisible again if you go to another sheet, this part works also fine.



What I am trying to do is, when I forget to click again on the button, now with the name Undo Filter, that the name changes to Set filter and makes the button Print invisible (this part works with your code). But the problem is that the active filter set in table5 doesn’t go back to a table with normal size (no filter in the table).

 Private Sub Worksheet_Deactivate()
cmb_Filter.Caption = "Set Filter"
CommandButton1.Visible = False
 'ActiveSheet.ListObjects("Tabel5").ShowAllData
End Sub

Maybe when you try it on “Test deactivate” it is more easy to explain?

Click first on the button Set filter

Click to open sheet 2 en go back to sheet 1

The button is back to Set Filter (OK), the print button is invisible (OK), but the active filter in the table stays.

I hope this is more clear.
Marc

[Tommy]

Here ya go!

I think I have attached the file.

[samabert]

Hello Tommy,

Thank you very much for your answer, this is completely what I wanted.
The example with your code works fine, but I have transposed it to my worksheet and now I have a fault and I can’t figure out why.

Worksheets("List").ListObjects("Tabel3").Range.AutoFilter

The Subscript is out of range.

Would you please be so kind to look at the example “My_table” to see what I’m doing wrong?

Thank you in advance
Marc

[Tommy]

I fought with this for about an hour before I realized Table was misspelled.

So try:

Worksheets("List").ListObjects("Table3").Range.AutoFilter

[SamT]

[samabert]

Shame on me! Sorry for your lost time about something that I should have seen immediately!
When you insert a table in ”Excel Dutch” it gets the name tabel.

Thank you very much for your help.
Marc

[Tommy]

Thanks for the information!