Creating CheckBoxes with VBA results in random order

[InsatiableAmos]

I have some VBA (using Excel 2007) that deletes and re-creates a bunch (20-ish) checkboxes in a spreadsheet. Since i'm using the controls-based checkboxes, Excel defaults them to object names of "CheckBox1, CheckBox2, CheckBox3, etc" as I create them the first time around.

Later on, i clear all checkboxes and implement the SAME checkbox-creating code to re-create all or some of the checkboxes, but Excel doesn't number them in order the second time around! I end up with something like "CheckBox2, CheckBox19, CheckBox3, CheckBox12, etc."

What could be causing my code to create things out of order the second time around? Is there a way to reset Excel's Checkbox-numbering iterator?

The code i'm using to create checkboxes is:

Dim x As Long
Dim BoxCell As String

BoxCell = ""
x = 0
'j gets passed into the sub as a "Long" somewhere between the values of 5 and 35

Do While x < j
        
        BoxCell = "C" & x * 4 + 13
        
        With Range(BoxCell)
            l = .Left
            t = .Top
        End With

        ActiveSheet.OLEObjects.Add(ClassType:="Forms.CheckBox.1", Link:=False, _
        DisplayAsIcon:=False, Left:=l, Top:=t, Width:=100, Height:= _
        16).Select
        With Selection
            .LinkedCell = BoxCell
        End With
    
        x = x + 1
    
    Loop

And the code i'm using to delete my checkboxes is:

    Dim myObj As Object
    CountDels = 0
    For Each myObj In ActiveSheet.OLEObjects
    
        If InStr(1, myObj.Name, "CheckBox", vbTextCompare) Then
            myObj.Delete
        End If
 
    Next myObj

 

[baxtercavendish]

I've just upgraded to excel 2010, and using the code below in excel 2000 works. I opened up the file in 2010, and on the original sheets of the file, I can run this code. But when I add a new sheet, the code does nothing when I run it. Are there any options I need to adjust?

Sub checkbox()
Dim BoxCell As String
    Dim x As Long
    Dim l As Long
    Dim t As Long
    Dim CheckName As String
    Dim CheckNum As Integer
    Dim ix As Long
    BoxCell = ""
    x = 0
    l = 0
    t = 0
    ix = ActiveSheet.UsedRange.Row - 1 + ActiveSheet.UsedRange.Rows.Count
     
    CheckNum = 0

    'j is a Long between 5 and 35 passed into the sub from the procedure that calls it
    Do While x < ix - 2
        
        BoxCell = "K" & x * 1 + 3
        
        With Range(BoxCell)
            l = .Left
            t = .Top
        End With
        
        CheckNum = x + 1
        CheckName = "CheckBox" & CheckNum

        ActiveSheet.OLEObjects.Add(ClassType:="Forms.CheckBox.1", Link:=False, _
        DisplayAsIcon:=False, Left:=l, Top:=t, Width:=16, Height:= _
        16).Select
        With Selection
            .LinkedCell = BoxCell
            .Name = CheckName               'This applies the "code" name to the checkbox object and affects which macro it activates
            .Object.Caption = CheckName   'This applies the "shape" name to the checkbox object and affects how you select it later on
        End With
        
        x = x + 1
        Range(BoxCell).Value = False
    
    Loop
End Sub

 

[baxtercavendish]

Sorry, I am an idiot, the reason it didnt do anything is of course because there were no cells filled to determine the rowcount feature.

Works fine.