Max limit on checkboxes on an Excel Sheet 1

[nelson97]

Does anyone know the exact limit on checkboxes/controls on an Excel sheet?

Thanks

 

[Zathras]

Not sure, but I think you would run out of patience wiring them all to code before Excel would run out of check boxes.

Why not use something like this instead:

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
  If Not Intersect(Target, Range("A:A")) Is Nothing Then
    If Target.Count < 50 And Target.Columns.Count = 1 Then
      If Target.Text = "X" Then
        Target.Value = ""
      Else
        Target.Value = "X"
      End If
    End If
  End If
End Sub

Your users can use the mouse to toggle the "X" flag in the designated column (column A in this sample code) and your code can test for "X" or blank to process the user's request.

They can't re-click the same cell like a check box (They would have to click some other column first), but if you are considering hundreds of check boxes, this might be a better way to go.

It has an added feature in that multiple rows can be "checked" (or unchecked) at once by clicking and dragging with the mouse.

 

[nelson97]

Thanks for this great suggestion. Will save me invaluable time in trying to trigger the cells.
If I wanted to modify that code to address different ranges and use color and text, is this right:
If Not Intersect(Target, Range("D33,D7:L7,X5:BB5,etc")) Is Nothing Then
If Target.Count < 50 And Target.Columns.Count = 1 Then
If Target.Interior.ColorIndex = 23 Then
Target.Interior.ColorIndex = xlNone
Else
Target.Interior.ColorIndex = 23
Target.Text = "N"
End If
End If
End If
End Sub

Will the "target.count < 50" need to be increased? If I'm modifying a few hundred cells?

Thanks!

 

[Zathras]

Since your arrangement appears to be rowwise instead of columnwise as in my original post, and you appear to want to have the "check boxes" in multiple non-contiguous ranges, the code needs to be modified somewhat.

Perhaps something like this:

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
  If Not Intersect(Target, Union(Range("D3:P3"), Range("D7:L7"), _
                               Range("X5:BB5"))) Is Nothing Then
    If Target.Count < 50 And Target.Rows.Count = 1 Then
      If Target.Interior.ColorIndex = 23 Then
        Target.Interior.ColorIndex = xlNone
      Else
        Target.Interior.ColorIndex = 23
        Target.Value = "N"
      End If
    End If
  End If
End Sub

But you may want to change the Target.Value to something other than "N" on the "If" case (where you are setting the .ColorIndex to xlNone.)

The "target.count < 50" is only there to protect you from selecting everything if you select the entire row (or column in my original post.) If you don't want the user to be able to "click" multiple cells at the same time, you could use "target.count=1" so that the effect takes place only when a single cell is selected.