Excel Checkbox limit -- VBA errors

fgiesen

New Member
Joined
Jan 13, 2002
Messages
1
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.
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
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.
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,557
Latest member
richa mishra

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top