VBA Excel 2010 : problem with checkbox code name modification

Question

Hello,

I have a problem when I try to modify the code name (not the caption !) of a checkbox.

In the sample code at the bottom of this post, I delete the previous checkboxes if any and I create 2 new ones.

If I run this code from a brand new file, everything works fine. Both code name and caption are ok for the 2 checkboxes.

But if I save the file and run it another time, the code name of the first checkbox is not updated as shown below :

This means that this renaming method doesn't work if ActiveX controls are already present in the Excel file.

I use a similar code in my project. It contains many ActiveX controls like checkboxes, listboxes, command buttons ... and all of them need to be present when opening the Excel file. The user can bring some modifications that lead to the re-creation of few checkboxes. The simplified code below is used to do this. But this problem generates compilation errors. I can rename all the controls manually but this is quite painful so I would like to solve this problem.

Here is the code used :

***************************************************************************

Private Sub Workbook_Open()

    Dim OLEObj As OLEObject

    Dim Index As Byte

    Dim Chkbox As OLEObject

    Const CheckName = "MyCheckBox"

    'Delete previous objects

    For Each OLEObj In ActiveSheet.OLEObjects

        OLEObj.Delete

    Next OLEObj

    'Create 2 checkboxes

    For Index = 1 To 2

        'Add a new checkbox

        Set Chkbox = ActiveSheet.OLEObjects.Add(ClassType:="Forms.CheckBox.1", Left:=40, Top:=40 * Index, Width:=120, Height:=30)

        'Change the name and the caption of the checkbox

        Chkbox.Name = CheckName & Index

        Chkbox.Object.Caption = CheckName & Index

    Next

    'Destroy the object

    Set Chkbox = Nothing

End Sub

***************************************************************************

Any ideas ?

Thanks a lot for your help.

Answer 1

In fact, I have to split the deletion and creation processes into 2 different routines.

Adding a DoEvents was a good idea and I really don't understand why it doesn't solve the issue.

Your CPU is to fast and the Harddisk to slow... or something like that. :-)

When you run into the same trouble sometimes try call DoEvents 1st, then call the 2nd sub with OnTime after a second.

Private Sub Workbook_Open()

  DeleteChkbx

  DoEvents

  Application.OnTime Now + TimeSerial(0, 0, 1), "NewChkbx"

End Sub

That should give the system enough time to finish the work.

Andreas.

Answer 2

Add a

  DoEvents

after the 1st FOR loop has run.

Andreas.

Answer 3

Thank you Andreas but adding a "DoEvents" after "Next OLEObj" does not change anything

Answer 4

I finally found a solution to this problem.

In fact, I have to split the deletion and creation processes into 2 different routines.

Adding a DoEvents was a good idea and I really don't understand why it doesn't solve the issue. Anyway, it gave me a real clue to solve this issue. Thanks Andreas.

The following code is working :

****************************************************************

Private Sub DeleteChkbx()

    Dim OLEObj As OLEObject

    'Delete previous objects

    For Each OLEObj In ActiveSheet.OLEObjects

        OLEObj.Delete

    Next OLEObj

End Sub

Private Sub NewChkbx()

    Dim Index As Byte

    Dim Chkbox As OLEObject

    Const CheckName = "MyCheckBox"

    'Create 2 checkboxes

    For Index = 1 To 2

        'Add a new checkbox

        Set Chkbox = ActiveSheet.OLEObjects.Add(ClassType:="Forms.CheckBox.1", Left:=40, Top:=40 * Index, Width:=120, Height:=30)

        'Change the name and the caption of the checkbox

        Chkbox.Object.Caption = CheckName & Index

        Chkbox.Name = CheckName & Index

    Next

    'Destroy the object

    Set Chkbox = Nothing

End Sub

Public Sub Test()

    Call DeleteChkbx

    Call NewChkbx

End Sub

****************************************************************

Answer 5

Hi Andreas,

I tried to use your solution in my "real" project which is bigger and contains much more objects but it did not work.

But I noticed that if I run the macro a second time, the objects code names are always ok. So I started to look for another solution based on this observation.

If I modify the deletion procedure as shown below, it works all the time even the first time.

Public Sub DeleteObj(OLEObj As Object)

    'Create a dummy object and delete it immediately

    Set NewObj = OLEObj.Duplicate 

    NewObj.Delete

    'Delete the real object

    OLEObj.Delete

End Sub

Don't know what to conclude about that, but it does not seem to be linked to a speed mismatch between the CPU and the hard disk.

This workaround is the best solution I could find although my macro is a bit slower.

Thanks for your help

Eric