Adding Checkboxes to sheet via code

[Rasm]

I have a sheet with a >20K rows - in lets say column C I currently have the either True/False - this tells me if a row should be included in an action. But I would like the cell to contain a CheckBox - However the only way is by inserting a Control - hmmm - so below is my test code for inserting the checkboxes - it seems to work.

BUT will 20K checkbox controls make my sheet "unstable" - memory problems - freeze up and so on - any comments would me much appreciated.

I use 'option compare text' - so it dont matter if TRUE/FALSE is in upper/lower case.

Public Sub AddCheckboxes(ByVal ColManipulate As String, ByVal FirstCRow As Long, ByVal LastCRow As Long, ByVal ChkBoxCaption As String)
    'ColManipulate ----- This is the column that the checkbox should reside in
    'FirstCRow ---- First Row to have checkbox added
    'LastCRow  ---- Last Row to have checkbox added
    'ChkBoxCaption  ---- If you want caption - but cell is linked
    
    Dim myBox As CheckBox
    Dim myCell As Range
    Dim CellRange As String
    CellRange = ColManipulate & FirstCRow & ":" & ColManipulate & LastCRow
    With ActiveSheet
        For Each myCell In .Range(CellRange).Cells
            With myCell
                Astr = .Text
                Set myBox = .Parent.CheckBoxes.Add(Top:=.Top, Width:=.Width, Left:=.Left, Height:=.Height)
                With myBox
                    .LinkedCell = ColManipulate & myCell.Row
                    .Caption = ChkBoxCaption
                    If Astr = "True" Then .Value = 1 Else .Value = 0
                    .Name = "checkbox_" & myCell.Address(0, 0)
                End With
            End With
        Next myCell
    End With
End Sub

 

[Norie]

Not sure about unstable but 20,000 check boxes might make it into a very large file which takes rather a long time to open.

Also you might either need a fair amount of code or quite a bit of looping to deal with all those checkboxes.

Why not just use Data>Validation... with the List option?

I think you can even get that to display crosses and ticks if you play about with the font.

PS TRUE/FALSE are boolean, not sure how if Option Compare Text will affect them.

 

[Rasm]

ahhh _ I will check the validation & Font bit. My sheet is already a monster as I have approx 1K columns as well.

I respect to the Boolean - the actual values when originally imported files are text - all in capital - so when I add the checkbox and set the value by reading a text value from myCell - I make it case insensetive. But yes - when I click on the box it displays True/False - even thus I set the value with zero & one.