programmatically adding VB Active X control in Excel

[Thomas Baier]

Hello,

I've created a very simple Active X control in VB6. The control just
contains a button and will show a message box when the button is pressed.

I then try to programmatically add an instance of the control to a
worksheet. I am using the following piece of code:


Application.ActiveSheet.OLEObjects.Add(ClassType:="VBVBACall.SimpleControl")

The control is created but the message box is not shown when the button is
pressed. The control seems to be "deactived". When I manually enter design
mode and leave this mode again the control works as expected.

I think I must be missing something in the implementation because when
creating one of the MSForms controls (e.g. "Forms.CommandButton.1")
everything works fine.

Is there any FAQ/documentation on creating ActiveX controls for use with
Excel? E.g. I'd be interested which "special" properties are handled by
Excel. It seems that at least "Application" and "Value" properties get some
special treatment.

 

[Thomas Baier]

Hello group,

I've created a very simple Active X control in VB6. The control just
contains a button and will show a message box when the button is pressed.

I then try to programmatically add an instance of the control to a
worksheet. I am using the following piece of code:


Application.ActiveSheet.OLEObjects.Add(ClassType:="VBVBACall.SimpleControl")

The control is created but the message box is not shown when the button is
pressed. The control seems to be "deactived". When I manually enter design
mode and leave this mode again the control works as expected.

I think I must be missing something in the implementation because when
creating one of the MSForms controls (e.g. "Forms.CommandButton.1")
everything works fine.

I've at least found a workaround, but I really don't understand it.

The problematic code looks like

---
Dim lOLEObject As OLEObject

set lOLEObject =
Application.ActiveSheet.OLEObjects.Add(ClassType:="VBVBACall.SimpleControl")
---

One workaround to this problem seems to add some additional lines:

---
Dim lOLEObject As OLEObject

set lOLEObject =
Application.ActiveSheet.OLEObjects.Add(ClassType:="VBVBACall.SimpleControl")
lOLEObject.Visible = False
lOLEObject.Visible = True
---

I really have no idea, why this works, but by searching google I found out
that this may be a problem already encountered by lots of people.

Any idea why this works?