Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 2 of 2

Thread: Excel Checkbox limit -- VBA errors

  1. #1
    New Member
    Join Date
    Jan 2002
    Posts
    1
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

  2. #2
    Board Regular
    Join Date
    Aug 2003
    Location
    England
    Posts
    5,552
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Excel Checkbox limit -- VBA errors

    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.
    -------------------------
    Hope this is helpful.
    -------------------------

    Have been away from the forum for quite a while, but am now back.

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •