storing/recalling checkbox state 1

[EckyThump]

I have a series checkboxes on a userform,that run autofilters on an Excel speadsheet. if the user closes the form the filters remain on bu when the userform is recalled to change filters, none of the checkboxes are as they were when closed.
How can I / what is the best way to store there state???

 

[SkipVought]

Hi,

The Filters store the information. Initialize the Checkboxes from the current filter states.

Skip,
[sub]
[red][/red]
[/sub]

 

[adamstuff]

Ecky,
Here's what I do where I use a checkbox to hide a column

Private Sub UserForm_Activate()
    'Set value of chkboxes to last setting
    If Columns("Q:S").EntireColumn.Hidden = True Then
        chkbox1.Value = False
    Else
        chkbox1.Value = True
    End If
End Sub

HTH

Heisenberg was probably right.

 

[EckyThump]

AdamStuff
Thanks for that, not sure how I am going to incorporate resetting the check boxes for an advanced filter though.
Here is an example of what I am trying to acheive:
I have two checkboxes (in reality there are four or five).
when I check a box it filters the column (thats the easy part).
It wors as a compound filter too (again this is what I want).
Here is the code:

Private Sub CheckBox1_Click()
 If CheckBox1 Then
    Selection.AutoFilter Field:=5, Criteria1:="<>"
 Else
    Selection.AutoFilter
 End If
End Sub

Private Sub CheckBox2_Click()
 If CheckBox2 Then
    Selection.AutoFilter Field:=7, Criteria1:="<>"
 Else
    Selection.AutoFilter
 End If
End Sub

Now for what doesn't work:
1. When I uncheck a checkbox I want it to rerun the filter for the remaining checkboxes (it doesnt at the moment).
2. When I uncheck the last box I want to remove the autofilter altogether (at the moment it shows to autofilter arrows on each column even though there is no filter).

What is the most efficient way to do this?
I tried to implement a select case statement, but didnt know how to get it to work.
Any ideas??

 

[SkipVought]

"1. When I uncheck a checkbox I want it to rerun the filter for the remaining checkboxes (it doesnt at the moment)."
[blus]
Unless you have Calculation set to MANUAL, changing one filter does not affect other filters. Just remove the criteria from the UNCHECKED filter, and the other filters will function as per each criteria.
[/blue]
"2. When I uncheck the last box I want to remove the autofilter altogether (at the moment it shows to autofilter arrows on each column even though there is no filter)."
[blus]
Turn on your macro recorder and record removing AutoFilter. Observe your recorded code.
[/blue]


Skip,
[sub]
[red][/red]
[/sub]

 

[EckyThump]

SkipVought
Firstly thanks for the reply.
I must be missing something, so excuse my ignorance.
I have calculations set to auto.
"Just remove the criteria from the unchecked filter"??
Each of the columns have values in and some have none, all the filter is doing is filtering out rows with no values.
The criteria is hard coded for each of the columns in the checkbox click() as above, so I am not sure how I am supposed to remove the criteria.
If two boxes are ticked and I remove one check then the whole both filters are removed but a check remains in one of the check boxes, and that filter is not applied?
My code now looks like this on the sheet holding the checkboxes:

Private Sub CheckBox1_Click()
 If CheckBox1 = True Then
    Range("K2").Select
    Selection.AutoFilter Field:=11, Criteria1:="<>"
  End If
End Sub

Private Sub CheckBox2_Click()
If CheckBox2 = True Then
    Range("J2").Select
    Selection.AutoFilter Field:=10, Criteria1:="<>"
  End If
End Sub

Private Sub CheckBox3_Click()
If CheckBox3 = True Then
    Range("I2").Select
    Selection.AutoFilter Field:=9, Criteria1:="<>"
  End If
End Sub

Private Sub CheckBox4_Click()
If CheckBox4 = True Then
    Range("G2").Select
    Selection.AutoFilter Field:=7, Criteria1:="<>"
  End If
End Sub

Private Sub CheckBox5_Click()
If CheckBox5 = True Then
    Range("E2").Select
    Selection.AutoFilter Field:=5, Criteria1:="<>"
  End If
End Sub

 

[EckyThump]

OK Code now:

Private Sub CheckBox1_Click()
 If CheckBox1 = True Then
    Range("K2").Select
    Selection.AutoFilter Field:=11, Criteria1:="<>"
 Else
    Selection.AutoFilter Field:=11
 End If
End Sub

Private Sub CheckBox2_Click()
If CheckBox2 = True Then
    Range("J2").Select
    Selection.AutoFilter Field:=10, Criteria1:="<>"
Else
    Selection.AutoFilter Field:=10
End If
End Sub

Private Sub CheckBox3_Click()
 If CheckBox3 = True Then
    Range("I2").Select
    Selection.AutoFilter Field:=9, Criteria1:="<>"
 Else
    Selection.AutoFilter Field:=9
 End If
End Sub

Private Sub CheckBox4_Click()
 If CheckBox4 = True Then
    Range("G2").Select
    Selection.AutoFilter Field:=7, Criteria1:="<>"
 Else
    Selection.AutoFilter Field:=7
 End If
End Sub

Private Sub CheckBox5_Click()
 If CheckBox5 = True Then
    Range("E2").Select
    Selection.AutoFilter Field:=5, Criteria1:="<>"
 Else
    Selection.AutoFilter Field:=5
 End If
End Sub

resetting filters that are checked now works (Thanks skipvought!)
cant get the autofilter arrows to go away when the last chekbox is cleared though.
When the last one is cleared, the filter is removed, but the arrows still displayed. going to menu/Data/Filter shows autofilter greyed out?
Any ideas? nearly there!

 

[PHV]

What about using the VisibleDropDown named argument of the AutoFilter method ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[EckyThump]

Ok that works for the selected column, but when the filter is apllied all the column headers have the arrow, is there a way to hide all the arrows?

 

[SkipVought]

Are you not filtering on some VALUE?

Private Sub CheckBox1_Click(sValue as String)
  Dim sCrit as string
  If CheckBox1 = True Then
     sCrit = "=" & sValue
  Else
     sCrit = "<>"
  End If
  Range("K2").AutoFilter _
     Field:=11, _
     Criteria1:=sCrit
End Sub

"If two boxes are ticked..."

The ALSO in each click event, you have to test ALL the CheckBox states.


Skip,
[sub]
[red][/red]
[/sub]

 

[EckyThump]

SkipVought
Something you said about checking all states promted a rethink of the logic.
It now works as it should although somewhat slow on some of the filters.
I am not filtering on any values but actually the opposite, filtering where the cell is null or empty. Like manually selecting non-blanks from the filter dropdown.
code is now:

Private Sub CheckBox1_Click()
 If CheckBox1 = True Then
    Range("K2").Select
    Selection.AutoFilter Field:=11, Criteria1:="<>"
 ElseIf CheckBox2 Or CheckBox3 Or CheckBox4 Or CheckBox5 Then
    Selection.AutoFilter Field:=11
 Else
    Selection.AutoFilter
 End If
End Sub

Private Sub CheckBox2_Click()
If CheckBox2 = True Then
    Range("J2").Select
    Selection.AutoFilter Field:=10, Criteria1:="<>"
ElseIf CheckBox1 Or CheckBox3 Or CheckBox4 Or CheckBox5 Then
    Selection.AutoFilter Field:=10
 Else
    Selection.AutoFilter
End If
End Sub

Private Sub CheckBox3_Click()
 If CheckBox3 = True Then
    Range("I2").Select
    Selection.AutoFilter Field:=9, Criteria1:="<>"
ElseIf CheckBox1 Or CheckBox2 Or CheckBox4 Or CheckBox5 Then
    Selection.AutoFilter Field:=9
 Else
    Selection.AutoFilter
End If
End Sub

Private Sub CheckBox4_Click()
 If CheckBox4 = True Then
    Range("G2").Select
    Selection.AutoFilter Field:=7, Criteria1:="<>"
ElseIf CheckBox1 Or CheckBox2 Or CheckBox3 Or CheckBox5 Then
    Selection.AutoFilter Field:=7
 Else
    Selection.AutoFilter
End If
End Sub

Private Sub CheckBox5_Click()
 If CheckBox5 = True Then
    Range("E2").AutoFilter Field:=5, Criteria1:="<>"
ElseIf CheckBox1 Or CheckBox2 Or CheckBox3 Or CheckBox4 Then
    Selection.AutoFilter Field:=5
 Else
    Selection.AutoFilter
End If
End Sub

Thanks for you help guys,
Two heads are better than one!

 

[SkipVought]

Great!

Skip,
[sub]
[red][/red]
[/sub]