Checkbox limitations

[Solution4U]

This appears to be a repeatable problem.

Please advise if there is a fix for this, my email is (e-mail address removed)

Here are the 2 posts from the forum:

I have 5 columns with over 100 check boxes in each. I keep getting an error
when the code runs unless I delete a certain portion of checkboxes. Right
now all is running well with 1195 checkboxes but if I add more I get the
error.

Does anyone know why this is happening or how to get around it?

I was only able to add 1098 check boxes using the code below with Excel 2003.
I could not find any limits listed in the excel help that explains this
problem.


Sub Macro1()
'
' Macro1 Macro
' Macro recorded 10/13/2008 by Joel
'

'
LeftPos = Range("B1").Left

For RowCount = 2 To 4000 Step 2
TopPos = Range("B" & RowCount).Top
ActiveSheet.OLEObjects.Add _
ClassType:="Forms.CheckBox.1", _
Link:=False, _
DisplayAsIcon:=False, _
Left:=LeftPos, _
Top:=TopPos, _
Width:=108, _
Height:=19.5
Next RowCount
End Sub




----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/com...34d7e02&dg=microsoft.public.excel.programming

 

[Dave Peterson]

First, I would never use 1000 checkboxes on a worksheet. But if I had to, I
wouldn't use the checkboxes from the Control Toolbox Toolbar. Those controls
are very "intensive" in excel.

I'd use the checkboxes from the Forms toolbar. In my experience, the controls
from the Forms toolbar behave better and impact excel less. (But I still
wouldn't use that many!)

This added 2000 checkboxes with no problems:

Option Explicit
Sub LayOutCheckboxes()

Dim myCBX As CheckBox
Dim myCell As Range
Dim wks As Worksheet
Dim iCtr As Long

Set wks = ActiveSheet

Application.ScreenUpdating = False

iCtr = 0
With wks
.CheckBoxes.Delete 'nice for testing
For Each myCell In .Range("b1:b2000").Cells
With myCell
.NumberFormat = ";;;" 'hide the true/false
iCtr = iCtr + 1
If iCtr Mod 50 = 0 Then
DoEvents
Application.StatusBar _
= "Processing: " & myCell.Address(0, 0)
End If
Set myCBX = .Parent.CheckBoxes.Add _
(Top:=.Top, Width:=.Width, _
Left:=.Left, Height:=.Height)
With myCBX
.LinkedCell = myCell.Address(external:=True)
.Caption = ""
.Name = "CBX_" & myCell.Address(0, 0)
'.OnAction = "'" & ThisWorkbook.Name & "'!CbxClick"
End With
End With
Next myCell
End With

With Application
.StatusBar = False
.ScreenUpdating = False
End With
End Sub

I did notice a slowdown on my pc when I got to about 1500 checkboxes.

 

[Solution4U]

I cannot use the checkbox from the forms toolbar because I need them to hide
along with the column they are in. I don't like using this many checkboxes
either but this is for an extensive checklist and it is tough to have a
checklist without checkboxes.

Unless you know of a way to hide the forms toolboxes?

 

[Dave Peterson]

It's a pain, but you could change the visibility to false. But then when you
show the row (and the checkbox), you'll probably have to reposition them.

I wouldn't use either checkbox. I'd just use a cell and format it to show a
check mark:

(Saved from a previous post.)

You may find it easier to use a cell and a Y or X or any character to indicate
yes. You'll be able to sort, filter, and even count these easier than using
checkboxes.

An alternative that I like:

Select the range that would have held the checkboxes.
Format|cells|number tab|custom category
In the "type:" box, put this:
alt-0252;alt-0252;alt-0252;alt-0252

But hit and hold the alt key while you're typing the 0252 from the numeric
keypad.

It should look something like this when you're done.
ü;ü;ü;ü
(umlaut over the lower case u separated by semicolons)

And format that range of cells as Wingdings (make it as large as you want)

Now, no matter what you type (spacebar, x, anyoldtextatall), you'll see a check
mark.

Hit the delete key on the keyboard to clear the cell.

If you have to use that "checkmark" in later formulas:
=if(a1="","no checkmark","Yes checkmark")

Or you can filter by blanks and non-blanks.