How to delay or postpone code execution in VBA

Question

Scenario

Create a blank workbook and add a ToggleButton and an InkPicture control (the latter inserted by means of ActiveX Controls -> More Controls). In the SizeChanged event of the InkPicture control, add the line:

Sheet1.ToggleButton1.Caption = "foo bar"

Problem

A "Method or data member not found" error in the line above that highlights the occurence of .ToggleButton1 from that line.

Reason

The ink picture's SizeChanged event happens before the toggle button is created, and in some cases, the event occurs before the workbook's Open event! You can Debug.Print relevant messages in the Immediate Window to see that.

Question

How can I postpone the execution of the ink picture's SizeChanged code until the toggle button is created and can be accessed as a property of the worksheet? I can, of course, workaround the issue by creating a Boolean variable to avoid the InkPicture's SizeChanged event's execution until the end of the workbook's Open one, but this is only paper over the cracks and doesn't solve the actual issue.

What I've tried

• stopping the debugger, re-compiling the VBA project and saving it afterwards
• add DoEvents before the line above
• set the toggle button's AutoLoad property to True, in an attempt to create the toggle button before the ink picture

Every attempt appeared to work after saving and reopening the workbook, only to fail on subsequent saves and openings.

Note: I've edited my post to be more concise and easy to read, so try not to be overzealous and downvote a legitimate question...

Answer 1

Depending on which properties you need to access, you can try and grab a reference to your control via the Worksheet.OLEObjects collection.

Example:

Private Sub InkPicture1_Resize(Left As Long, Top As Long, Right As Long, Bottom As Long)
    Dim oleObj As OLEObject

    Set oleObj = Sheet1.OLEObjects("ToggleButton1")
    oleObj.Left = 1
    oleObj.Top = 1
    '...
End Sub

In this way, you are interacting with the control through the OLEObject Interface. As such, some of the control properties may not be available. Normally, you can interact with the control through its specific control interface (ie, the ToggleButton interface) by grabbing the OLEObject.Object property.

Private Sub InkPicture1_Resize(Left As Long, Top As Long, Right As Long, Bottom As Long)
    Dim tb As MSForms.ToggleButton

    Set tb = Sheet1.OLEObjects("ToggleButton1").Object

    tb.Caption = "foo"
End Sub

However, this seems to cause an error when the workbook is starting up. I believe it is a security thing. But, at least this error is a runtime that you can catch and handle, as oppose to the unhandlable Method or data member not found compiler error you are seeing right now.

Hope this helps!