View Full Version : Solved: checkbox on spreadsheet
Emoncada
01-07-2008, 01:49 PM
I would like to have 3 checkboxes on a spreadsheet that will automatically filter a range by the value of the checkbox. I have the three checkboxes on the spreadsheet but I can't group them. So only one can be selected how can i do that?
Emoncada
01-07-2008, 02:25 PM
How can I group these so only one can be selected ? On A Spreadsheet
CheckboxMortgage, CheckboxDepot, CheckboxVam
Bob Phillips
01-07-2008, 02:50 PM
Use optionbuttons.
Emoncada
01-07-2008, 03:20 PM
Why is this not working
If OptButtonDepot.Value = True Then
Range("A2:O5000").Select
ActiveWindow.LargeScroll ToRight:=-1
Selection.AutoFilter
Selection.AutoFilter Field:=1, Criteria1:="Depot"
End If
Emoncada
01-07-2008, 03:24 PM
Its giving me object required.
With Master
If OptButtonMort.Value = True Then
Range("A2:O5000").Select
ActiveWindow.LargeScroll ToRight:=-1
Selection.AutoFilter
Selection.AutoFilter Field:=1, Criteria1:="Mortgage"
End If
If OptButtonDepot.Value = True Then
Range("A2:O5000").Select
ActiveWindow.LargeScroll ToRight:=-1
Selection.AutoFilter
Selection.AutoFilter Field:=1, Criteria1:="Depot"
End If
If OptButtonVam.Value = True Then
Range("A2:O5000").Select
ActiveWindow.LargeScroll ToRight:=-1
Selection.AutoFilter
Selection.AutoFilter Field:=1, Criteria1:="Vam"
End If
If OptButtonNone.Value = True Then
Range("A2:O5000").Select
ActiveWindow.LargeScroll ToRight:=-1
Selection.AutoFilter
Selection.AutoFilter Field:=1, Criteria1:="All"
End If
End With
Bob Phillips
01-07-2008, 03:24 PM
What sort of optionbutton is it? Forms toolbar, control toolbox, userform?
Emoncada
01-07-2008, 03:29 PM
Ok I changed to this
If ActiveSheet.Shapes("OptButtonMort").Value = True Then
Range("A2:O5000").Select
ActiveWindow.LargeScroll ToRight:=-1
Selection.AutoFilter
Selection.AutoFilter Field:=1, Criteria1:="Mortgage"
End If
If ActiveSheet.Shapes("OptButtonDepot").Value = True Then
Range("A2:O5000").Select
ActiveWindow.LargeScroll ToRight:=-1
Selection.AutoFilter
Selection.AutoFilter Field:=1, Criteria1:="Depot"
End If
If ActiveSheet.Shapes("OptButtonVam").Value = True Then
Range("A2:O5000").Select
ActiveWindow.LargeScroll ToRight:=-1
Selection.AutoFilter
Selection.AutoFilter Field:=1, Criteria1:="Vam"
End If
If ActiveSheet.Shapes("OptButtonNone").Value = True Then
Range("A2:O5000").Select
ActiveWindow.LargeScroll ToRight:=-1
Selection.AutoFilter
Selection.AutoFilter Field:=1, Criteria1:="All"
End If
now it's giving me a 'object doesn't support this property or method'
Emoncada
01-07-2008, 03:30 PM
control toolbox
Bob Phillips
01-07-2008, 04:21 PM
Control toolbox buttons have a click event associated with them. Plug into that.
Emoncada
01-07-2008, 05:19 PM
How can I make that work?
Emoncada
01-07-2008, 05:28 PM
I have the 4 optionbuttons and a go button. I want to be able to select one optionbutton then click go for it to run the filter.
Emoncada
01-07-2008, 05:51 PM
Ok i figured it out a way to do it
Sub MacroFilter()
With Master
Range("A2:O5000").Select
ActiveWindow.LargeScroll ToRight:=-1
Selection.AutoFilter
Selection.AutoFilter Field:=1, Criteria1:=Range("J1")
End With
End Sub
And in the workbook
Private Sub OptButtonDepot_Click()
If OptButtonDepot.Value = True Then
Range("$J$1").Value = "Depot"
End If
End Sub
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.