• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 252
  • Last Modified:

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
0
Fernando
Asked:
Fernando
  • 2
  • 2
  • 2
  • +2
5 Solutions
 
edwardiiiCommented:
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
 
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
nffvrxqgrcfqvvcCommented:
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
 
nffvrxqgrcfqvvcCommented:
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
 
rvooijsCommented:
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
 
edwardiiiCommented:
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
 
FernandoAuthor Commented:
Thanks for your time guys, but I stuck with my original solution.
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 2
  • 2
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now