PDA

View Full Version : [SOLVED] filtering data based on one column through macro buttons



Beatrix
09-04-2015, 09:10 AM
Hi Everyone

I designed a spreadsheet and users have got no excel skills at all. They need to press buttons to filter data based on one column. I know it is just basic filtering data thing but I have to design it this way. Can anyone recommend me a vba script to get this done? I attached a sample data scenario.

Cheers
B.

Aussiebear
09-04-2015, 02:55 PM
What's stopping you from teaching them to use the filtering process offered by Excel?

Beatrix
09-07-2015, 09:30 AM
I would love to teach them to use the filtering process but unfortunately it's not my decision to make. They need to press buttons through their ipad(!)
This was easy with macro. For vba newbies here what I used.


Sub Filter()
Range("B4").Select
Selection.AutoFilter
ActiveSheet.Range("$A$4:$H$11").AutoFilter Field:=2, Criteria1:="b"
End Sub

mancubus
09-07-2015, 01:22 PM
avoid select, activate when writing code. you seldom need them.

as you and aussiebear have stated what they need are on the ribbon.

i inserted two buttons to worksheet which are already on the ribbon. :)

i assume the criteria are in range B2:H2. so the user needs to select one of these cells and click/tap Apply Filter commandbutton.



Private Sub CommandButton1_Click()
'Applies filter to Column B
With ActiveSheet
.Range("B4").CurrentRegion.AutoFilter Field:=2, Criteria1:="=" & ActiveCell.Value
End With

End Sub




Private Sub CommandButton2_Click()
'Removes filter from Column B
With ActiveSheet
.AutoFilterMode = False 'remove existing filters, if any
End With
End Sub