PDA

View Full Version : Filtering on checkbox text



CJW_14
07-17-2018, 04:50 PM
Hi There,

I have following code that uses the text of form controls checkboxes to filter a list if they are checked.

I want to perform this exact same function but use activeX checkboxes and for the life of me I cant figure it out.

Any help would be greatly appreciated.


Dim arrList() As String, Cnt As Long

Cnt = 0
For Each CheckBox In ThisWorkbook.Sheets("Checkboxes").CheckBoxes
If CheckBox.Value = 1 Then
ReDim Preserve arrList(Cnt)
arrList(Cnt) = CheckBox.Text
Cnt = Cnt + 1
End If
Next
Sheets("Sheet1").Range("$A$8:$S$10000").AutoFilter Field:=5, Criteria1:=arrList, Operator:=xlFilterValues

georgiboy
07-18-2018, 12:01 AM
This may help:


Sub test()
Dim obj As Object
For Each obj In Sheet1.OLEObjects
If TypeName(obj.Object) = "CheckBox" Then
MsgBox obj.Name
End If
Next obj

End Sub

CJW_14
07-19-2018, 05:28 PM
Thanks mate, I mucked around with it for about a day and couldn't get what I needed. I ended up staying with form control checkboxes and changed the way I was running some subsequent things.

georgiboy
07-19-2018, 11:37 PM
Glad you got it sorted in the end.

I try not to use these objects as I use excel 2007 & 2010, one of which will not allow me to use them due to a glitch and the other (work) has blocked ActiveX altogether for security.

So I tend to stick to form based control's