This appears to be a repeatable problem.

Please advise if there is a fix for this, my email is (E-Mail Removed)

Here are the 2 posts from the forum:

"Solution4U" wrote:

> 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/comm...el.programming

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 wrote:
>
> This appears to be a repeatable problem.
>
> Please advise if there is a fix for this, my email is (E-Mail Removed)
>
> Here are the 2 posts from the forum:
>
> "Solution4U" wrote:
>
> > 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/comm...el.programming

--

Dave Peterson

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" wrote:

> 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 wrote:
> >
> > This appears to be a repeatable problem.
> >
> > Please advise if there is a fix for this, my email is (E-Mail Removed)
> >
> > Here are the 2 posts from the forum:
> >
> > "Solution4U" wrote:
> >
> > > 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/comm...el.programming
>
> --
>
> 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.

Solution4U wrote:
>
> 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" wrote:
>
> > 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 wrote:
> > >
> > > This appears to be a repeatable problem.
> > >
> > > Please advise if there is a fix for this, my email is (E-Mail Removed)
> > >
> > > Here are the 2 posts from the forum:
> > >
> > > "Solution4U" wrote:
> > >
> > > > 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/comm...el.programming
> >
> > --
> >
> > Dave Peterson
> >

--

Dave Peterson