Filtering on cells with checkboxes

[quercus]

I've found this code which works a treat in adding multiple checkboxes to a selection of cells on a worksheet. The checkboxes are also linked to the cells.

Unfortunately, it doesn't seem to filter as I would expect - it appears to only have 'TRUE'/'Blanks' as options whereas I was expecting to be able to filter with the TRUE/FALSE values. Even with the true/blanks the filter doesn't work.

Is there a work around or should I consider using an alternative to the checkbox?

Sub AddCheckBoxes()
On Error Resume Next
Dim c As Range, myRange As Range
Set myRange = Selection
For Each c In myRange.Cells
ActiveSheet.CheckBoxes.Add(c.Left, c.Top, c.Width, c.Height).Select
With Selection
.LinkedCell = c.Address
.Characters.Text = ""
.Name = c.Address
End With
Range(c, c.Offset(, 1)).Select
With Selection
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, _
Formula1:="=" & c.Address & "=TRUE"
.FormatConditions(1).Interior.ColorIndex = 6
End With
With c
.FormatConditions(1).Font.ColorIndex = 6
.Font.ColorIndex = 2
End With
Next
myRange.Select
End Sub

thanks
Quercus

 

[Marcelo Branco]

Hi Quercus,

Supposing you have created a check-box in cell A1 you can use in Excel
=IF(A1,"something","other")

Or in VBA

If Range("A1") Then
        ' do some stuff
Else
        ' do other stuff
End If

HTH

M.