Adam has attended:
Excel VBA Intro Intermediate course
Auto Filtering using results from toggle button
Hi Guys,
I am trying to design a user form that i can use to auto filter data on a sheet in my workbook.
Basically i have 36 toggle buttons which can be on or off and i want the values if true to be passed on to an autofilter criteria.
Any suggestions on the best way to achieve this?
Thanks
Adam
RE: Auto Filtering using results from toggle button
Hi Adam
Thanks for getting in touch.
You can take the value of the toggle (option button, check box etc) and embed it in a SELECT CASE structure.
Have a look at this example, where an option button control changes whether to show January's or February's data:
SELECT CASE optionbutton1.value
CASE TRUE
ActiveSheet.Range("$A$1:$X$69").AutoFilter Field:=3, Criteria1:= _
"January"
CASE FALSE
ActiveSheet.Range("$A$1:$X$69").AutoFilter Field:=3, Criteria1:= _
"February"
END SELECT
And you could modify the range to something more dynamic, change the field number to the relevant field and change the criteria to a variable if appropriate.
I hope this helps, let us know how you get on.
Kind regards
Gary Fenn
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
RE: Auto Filtering using results from toggle button
Hi Gary,
Thanks for getting back in touch. in the end I managed to use code that wrote a value into a cell for each toggle button.
If toggleGun1.Value = True Then
toggleGun1.BackColor = RGB(255, 0, 0)
Sheets("Sheet1").Range("A1") = 1
Else
toggleGun1.BackColor = RGB(0, 255, 0)
Sheets("Sheet1").Range("A1") = ""
End If
Your way looks a bit silkier though!
Cheers
Adam
