Same action to all command buttons on selected sheets?

Hi There,

I have a workbook that has a database of products on a number of sheets. Each of the sheets that have the products listed has Auto Filter enabled. Currently I have command buttons to go to different areas in the workbook and a button to Show All data (If)...

Private Sub bttn01_home_Click()
Sheets("Welcome").Select
End Sub


Private Sub bttn02_back_Click()
Sheets("Welcome").Select
End Sub

Private Sub bttn03_help_Click()
Sheets("Help Sub").Select
End Sub

Private Sub bttn04_ex_Click()
Sheets("ExM Adder").Select
End Sub

Private Sub bttn05_showall_Click()
  If ActiveSheet.FilterMode Then
  ActiveSheet.ShowAllData
  End If
End Sub

I need to reset the filter apon leaving the current sheet if the filter is active. I can add the ShowAllData function to each button and it works exactly as I would like it to. But, the question is...

Can I add some code that will perform this function on every commandbutton on the selected sheet, rather than having to go through every sheet and adding the function to every button like below: (Excluding the button that already performs the ShowAllData function.)

Private Sub bttn01_home_Click()
  If ActiveSheet.FilterMode Then
  ActiveSheet.ShowAllData
  'ShowAllData added to suit Search Function
  End If
Sheets("Welcome").Select
End Sub


Private Sub bttn02_back_Click()
  If ActiveSheet.FilterMode Then
  ActiveSheet.ShowAllData
  End If
Sheets("Welcome").Select
End Sub

Private Sub bttn03_help_Click()
  If ActiveSheet.FilterMode Then
  ActiveSheet.ShowAllData
  End If
Sheets("Help Sub").Select
End Sub

Private Sub bttn04_ex_Click()
  If ActiveSheet.FilterMode Then
  ActiveSheet.ShowAllData
  End If
Sheets("ExM Adder").Select
End Sub

Private Sub bttn05_showall_Click()
  If ActiveSheet.FilterMode Then
  ActiveSheet.ShowAllData
  End If
End Sub

Thanking you in advance
FernandoAsked:
Who is Participating?
 
edwardiiiConnect With a Mentor Commented:
Well, I'm not sure why it's not working for your workbook (I keep getting "cannot load .dll" and "Excel has generated errors"), but I can take large workbooks I've created and insert the following code in the VBAProject/Microsoft Excel
Objects/ThisWorkbook/Workbook.SheetActivate or SheetDeactivate event:

     If ActiveSheet.FilterMode Then
          ActiveSheet.ShowAllData
     End If

Basically if I tinker with the filter settings and move to another page, then come back to that page, it will enable
ShowAllData.

Hopefully it will work for you when you try it on your workbook.
0
 
edwardiiiConnect With a Mentor Commented:
hi, burkertadmin.

I'm not clear--are the command buttons in the worksheets, and you've assigned VBA code to each one?  Or do you have
a UserForm with various command buttons with the above code in them?  My first thought, to be modified by your answer to the above question, is something like this:
       
        With ActiveSheet.FilterMode
            ActiveSheet.ShowAllData
        End With
   


0
 
FernandoAuthor Commented:
This is the original worksheet:

http://www.uctrlit.com/gbzhhu.zip

Everytime some exits from a product list sheet, whether it be back to the main page, or to a help page or wherever, the filter needs to be reset to ShowAllData if the filter was used during that session.

Where would I add the code if it is the one you mentioned above?
0
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

 
ShelfieldCollegeCommented:
Not really sure I quite understand what you mean, but could you use the activate event of the help sheets etc to clear any filters in place?
0
 
nffvrxqgrcfqvvcConnect With a Mentor Commented:
What about using a combobox?? and whenever the combo item changes it refreshes?? just a suggestion..something like this...


Private Sub Combo1_Change()
If ActiveSheet.FilterMode Then
  ActiveSheet.ShowAllData
  End If
End Sub

Private Sub Command1_Click()
Select Case Combo1

Case "HOME"
'home code
Sheets("Welcome").Select
Case "HELP"
'help code
Sheets("Help Sub").Select
Case "BACK"
'back code
Sheets("Welcome").Select
Case "EXIT"
'exit code
Sheets("ExM Adder").Select
Case "SHOW ALL"
'show all code
  If ActiveSheet.FilterMode Then
  ActiveSheet.ShowAllData
  End If
 
End Select

End Sub

Private Sub Form_Load()
Combo1.AddItem "HOME"
Combo1.AddItem "HELP"
Combo1.AddItem "BACK"
Combo1.AddItem "EXIT"
Combo1.AddItem "SHOW ALL"

End Sub
0
 
nffvrxqgrcfqvvcConnect With a Mentor Commented:
Sorry rather than adding it to Combo1_Change

You should add it the click event       Combo1_Click
'-------------

Private Sub Combo1_Click()
'Change or refresh data
  If ActiveSheet.FilterMode Then
  ActiveSheet.ShowAllData
doevents
  End If

End Sub
0
 
rvooijsConnect With a Mentor Commented:
Hi,

Here's a quick and dirty soluttion.

Create a public sub:
    Public Sub UndoFilter()
      If ActiveSheet.FilterMode Then
        ActiveSheet.ShowAllData
      End If
    End Sub

Now do a Find-And-Replace (ctrl-H):
    find:      Sheets(
    replace: UndoFilter : Sheets(

You should do the replace One-By-One, in case there are some sub's that should not be changed, but with only a few clicks you will have changed all On_click events.

Success,
Robert
0
 
FernandoAuthor Commented:
Thanks for your time guys, but I stuck with my original solution.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.