asked on

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

SOLUTION

edwardiii

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Fernando

ASKER

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?

ShelfieldCollege

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?

SOLUTION