Excel Checkbox limit -- VBA errors

[fgiesen]

Problem:
When check boxes are created they are assigned a name "check box ######" Where ##### increments from 1 - n. VB will create check boxes greater than 65535, but will error on a select method. The check box numbering sequence continues through the life of the spread sheet.
Is it possible to completely remove all knowledge of an object?
If you take this VB code, run the macro, let it run till it gets to 65535 check boxes and you'll see the error. "Select method of CheckBox class failed" on the 65536'th.

The workaround is to delete the work sheet, make new sheet which restarts the numbering. (in 6 months to a year you'll get to fix it again!)

to duplicate the symptom let this run till it stops with the error

Sub make_site_chk_box()
'
k = 1
Do While k < 66000
i = 1

Do While i < 10
' rt, dn,width, height
ActiveSheet.CheckBoxes.Add(i * 65, 0, 50, 20).Select
With Selection
.name = "box " & k
.Characters.Text = "box " & k
.LinkedCell = a10
.OnAction = "rm_chk_boxes"
End With
i = i + 1
k = k + 1
Loop
ActiveSheet.CheckBoxes.Delete

Loop
Range("A1").Select

End Sub


Verified on Office 2000 and Office XP.

Fred.

[onlyadrafter]

Hello,

I know you posted this a long time ago, but I have only recently registered, and am trying to work my way through the unanswered posts, to gain as much experience as possible. I don't know if you need this any more, but I think I have come up with a solution.

Try this code, it worked for mw in Excel 97.

Sub Macro12()
K = 1
Do While K < 66000
I = 1
Do While I < 10
ActiveSheet.Shapes("Check Box 1").Select
Selection.ShapeRange.Duplicate.Select
Selection.ShapeRange.IncrementLeft 60# * I
Selection.ShapeRange.IncrementTop -75#
Selection.Name = "BOX NO " & K
Selection.Characters.Text = "BOX " & K
Selection.LinkedCell = a10
Selection.OnAction = "rm_chk_boxes"
I = I + 1
K = K + 1
'THESE LINES JUST TO TIDY UP
If K > 5 Then
L = K - 3
ActiveSheet.Shapes("BOX NO " & L).Delete
End If
'THESE LINES JUST TO TIDY UP
Loop
'ActiveSheet.CheckBoxes.Delete
Loop
Range("A1").Select
End Sub

Hope this helps.