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

 

[InsatiableAmos]

No one knows how Excel decides to name checkboxes as they're created?

What additional information do i need to provide, if any?

 

[Gary McMaster]

I used your code to add and remove 35 check boxes about 10 times. I did not see the behavior you mentioned (XL 2007).

You didn't make any mention of changing the names of the controls after they are inserted. That can sometimes cause problems if you are unaware of the "Dual Names" snafu. Here is a link to the 2007 help topic that mentions it.

http://office.microsoft.com/client/helppreview.aspx?AssetID=HV100384931033&ns=EXCEL.DEV&lcid=1033

In case the link doesn't work, here's some of the text:

Using Control Names with the Shapes and OLEObjects Collections

An ActiveX control on a sheet has two names: the name of the shape that contains the control, which you can see in the Name box when you view the sheet, and the code name for the control, which you can see in the cell to the right of (Name) in the Properties window. When you first add a control to a sheet, the shape name and code name match. However, if you change either the shape name or code name, the other is not automatically changed to match.

You use the code name of a control in the names of its event procedures. However, when you return a control from the Shapes or OLEObjects collection for a sheet, you must use the shape name, not the code name ...

Gary

 

[InsatiableAmos]

Hah! I had already kind of discovered that quirk on my own as i learned about ActiveX control checkboxes in 2007, but it's nice to read an official MS page about it.

While this doesn't solve my issue directly (i'm changing neither the Shape name, nor the code name in my code), there's a chance i could build a workaround using this information.

How do i update the CODE name for a checkbox using VBA? There's plenty of info on how to update the shape name, but if i can just reset both the code and shape names to whatever i want as i create the objects, then my issue will be solved.

 

[Gary McMaster]

I don't think you can change the "Code Name" (if I have the terminology straight). That was the jist of the help passage as I read it.

I did some work with "Form check boxes" some time ago and wanted to get hold of the "actual checkbox object" rather than just the shape. I came up with the following:

(Please run on empty book/sheet)

Sub CheckBox_Demo_Create()
 
Dim oShape As Shape
Dim oActive As Worksheet
Dim oCheckBox As CheckBox
Dim oRange As Range
Dim oCell As Range
Dim lWidth As Long
Dim lHeight As Long
 
lWidth = 100
lHeight = 12
 
Set oActive = ActiveSheet
Set oRange = oActive.Range("B1:B10")

oRange.ColumnWidth = 16
oRange.Offset(0, 1).ColumnWidth = 60
 
For Each oShape In oActive.Shapes
    If InStr(1, oShape.Name, "CheckBox") Then
        oShape.Delete
    End If
Next oShape
 
For Each oCell In oRange
    Set oCheckBox = oActive.CheckBoxes.Add(oCell.Left, oCell.Top, lWidth, lHeight)
    With oCheckBox
        '.LinkedCell = oCell.Address
        '.Interior.ColorIndex = 3
        .Name = "CheckBox_" & oCell.Address
        .Caption = .Name
        .OnAction = "Checkbox_Toggle"
    End With
Next oCell
 
End Sub

After you create them on a clean sheet you can retreive the actual object with the second half of the following:

Public Sub Checkbox_Toggle()
 
Dim oShape As Shape
Dim oCell As Range
Dim oActive As Worksheet
 
Set oActive = ActiveSheet
 
Set oShape = oActive.Shapes(Application.Caller) 'Referenced as shape (see below)
 
Set oCell = oShape.TopLeftCell
 
If oShape.ControlFormat.Value = Checked Then
    oCell.Offset(0, 1).Value = "The check box in cell" & oCell.Address & " named " & oShape.Name & " was checked"
Else
    oCell.Offset(0, 1).Value = "The check box in cell" & oCell.Address & " named " & oShape.Name & " was un-checked"
End If

'If the name of the "Check Box" was changed after insertion it can no longer
'be reference by name as "Check Box" object. Use the following to retrieve
'the actual "Check Box" object by its index number
 
Dim oCheckBox As CheckBox
Dim iBoxCount As Integer
 
For iBoxCount = 1 To oActive.CheckBoxes.Count
    If oActive.CheckBoxes(iBoxCount).Name = Application.Caller Then
        Set oCheckBox = oActive.CheckBoxes(iBoxCount)
        Exit For
    End If
Next iBoxCount
 
If Not oCheckBox Is Nothing Then
    Debug.Print oCheckBox.TopLeftCell.Address
    Debug.Print oCheckBox.BottomRightCell.Address
    Debug.Print oCheckBox.Caption
    Debug.Print oCheckBox.Border.LineStyle
    Debug.Print oCheckBox.Interior.ColorIndex
    Debug.Print oCheckBox.Enabled
    Debug.Print oCheckBox.OnAction
    'whatever else
End If
 
End Sub

I believe that something similar should work for the "ActiveX" version of the control. It is the dual name thing that stands in the way.

If you come up with a better way to grab the actual control object, I'd love to hear abour it.

Gary

 

[Gary McMaster]

I guess I misread that help passage. It appears you CAN change both names. They just don't synchronize automatically. If you change one you have to deliberately change the other if you want them to be the same.

It can get you banging your head against the wall if you're trying to address them by name and don't know about the dual name thing

I guess that was the whole point of my effort in trying to get hold of the actual control object from its shape object.

Gary

 

[InsatiableAmos]

While i was waiting for a reply (thanks for following through with this issue, Gary), i came up with a solution based on your information that works! Huge thanks for the help...

What i ended up doing was hunting down a way to change both the code name and the caption name for an ActiveX checkbox, and as part of my checkbox creation loop, i rename both properties right after creation. Here's the resulting code:

Dim BoxCell As String
    Dim x As Long
    Dim l As Long
    Dim t As Long
    Dim CheckName As String
    Dim CheckNum As Integer
        
    BoxCell = ""
    x = 0
    l = 0
    t = 0
    CheckNum = 0

    'j is a Long between 5 and 35 passed into the sub from the procedure that calls it
    Do While x < j
        
        BoxCell = "C" & x * 4 + 13
        
        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

 

[Gary McMaster]

Glad you got it working.

That seems to be the key if you want to address them by name ... keep the names the same. Probably easily done if you never let them get out of sync in the first place.

Gary

 

[InsatiableAmos]

Yea, the two types of names were always in sync, excel was just numbering them out of order...very confusing. Oh well, this'll do for now!

 

[Gary McMaster]

I have always had trouble getting Excel to sort numbers that are entered as text properly, probably everyone else too. Perhaps your problem was another by-product of that.

There seems to be some improvement in 2007 ... "If it looks like a number sort it like a number". I'm sure you've seen that dialog.

Gary