Modeless userform OLEObject Add problem

Hi,

I have a form that among other things tries to add OLEObject controls
to the active sheet.

The issue is that the userform disappears if there is a transaction
where a control needs to be added on the worksheet. No exit event
handlers (Deactivate, Terminate, Error) are fired when this happens.
If there is no necessity to add an OLEObject then the form works fine.
I tested it by commenting the OLEObject.add call. It worked fine.
I also inserted several debug messages and all procedures execute till
completion. After which the modal form disappear.
If the form is modal then it works fine.

I am including a skeleton code below. Working code that simulates the
problem.

This thing is really stumping me.
Thanks,
Jayant


Worksheet Button click that brings up the form (UF)
--------------------------------------------------
Private Sub cmd_Click()
UF.startLine = 10
UF.Show (vbModeless)
End Sub

Userform UF code
----------------
Public startLine As Long

Private Sub UserForm_Activate()
ll.Caption = "Add at Line: " & startLine
End Sub

Private Sub cmdAdd_Click()

Dim sht As Worksheet
Dim o As OLEObject
Dim rng As Range

Set sht = ActiveSheet
Set rng = sht.Range("A" & startLine)


Set o =sht.OLEObjects.Add(classtype:="Forms.CommandButton.1", _
Top:=rng.Top, Left:=rng.Left, Width:=rng.Width, _
Height:=rng.Height)
o.Name = "tempcmd_" & startLine

startLine = startLine + 1
ll.Caption = "Add at Line: " & startLine

End Sub

Private Sub UserForm_Deactivate()
MsgBox "I am not getting here"
End Sub

Private Sub UserForm_Error(ByVal Number As Integer, ByVal Description
As MSForms.ReturnString, ByVal SCode As Long, ByVal Source As String,
ByVal HelpFile As String, ByVal HelpContext As Long, ByVal
CancelDisplay As MSForms.ReturnBoolean)
MsgBox "I am not getting here"
End Sub

Private Sub UserForm_Terminate()
MsgBox "I am not getting here"
End Sub

Hi Jayant,

I replicate your problem, makes no difference running from an ActiveX button
or a Forms button. However no problem to add controls to any sheet EXCEPT
the activesheet.

Stephen Bullen replied to me in this thread:
Subject: running same code with multiple controls
Date: Jan 2005

> Unless I'm totally missing something (possible) it would appear there's a
> fundamental difference between running code from the wb to which new
> worksheet controls are being added, or to another workbook.

Absolutely. Adding an ActiveX control (from the Control Toolbox) to a sheet
causes the project of the workbook containing that sheet to recompile. If
that workbook is the same one that's running the code, bad things happen.
If that's a separate workbook, it'll just reset any module-level and global
variables in the project (i.e. including those used to hold 'withevents'
class references).

Stephen Bullen

Whilst the scenario in this thread is totally different to your problem, I
suspect Stephen's explanation applies similarly.

Regards,
Peter T

"jjk" <jayantjk@gmail.com> wrote in message
news:1117041944.607780.21850@g44g2000cwa.googlegroups.com...
> Hi,
>
> I have a form that among other things tries to add OLEObject controls
> to the active sheet.
>
> The issue is that the userform disappears if there is a transaction
> where a control needs to be added on the worksheet. No exit event
> handlers (Deactivate, Terminate, Error) are fired when this happens.
> If there is no necessity to add an OLEObject then the form works fine.
> I tested it by commenting the OLEObject.add call. It worked fine.
> I also inserted several debug messages and all procedures execute till
> completion. After which the modal form disappear.
> If the form is modal then it works fine.
>
> I am including a skeleton code below. Working code that simulates the
> problem.
>
> This thing is really stumping me.
> Thanks,
> Jayant
>
>
> Worksheet Button click that brings up the form (UF)
> --------------------------------------------------
> Private Sub cmd_Click()
> UF.startLine = 10
> UF.Show (vbModeless)
> End Sub
>
> Userform UF code
> ----------------
> Public startLine As Long
>
> Private Sub UserForm_Activate()
> ll.Caption = "Add at Line: " & startLine
> End Sub
>
> Private Sub cmdAdd_Click()
>
> Dim sht As Worksheet
> Dim o As OLEObject
> Dim rng As Range
>
> Set sht = ActiveSheet
> Set rng = sht.Range("A" & startLine)
>
>
> Set o =sht.OLEObjects.Add(classtype:="Forms.CommandButton.1", _
> Top:=rng.Top, Left:=rng.Left, Width:=rng.Width, _
> Height:=rng.Height)
> o.Name = "tempcmd_" & startLine
>
> startLine = startLine + 1
> ll.Caption = "Add at Line: " & startLine
>
> End Sub
>
> Private Sub UserForm_Deactivate()
> MsgBox "I am not getting here"
> End Sub
>
> Private Sub UserForm_Error(ByVal Number As Integer, ByVal Description
> As MSForms.ReturnString, ByVal SCode As Long, ByVal Source As String,
> ByVal HelpFile As String, ByVal HelpContext As Long, ByVal
> CancelDisplay As MSForms.ReturnBoolean)
> MsgBox "I am not getting here"
> End Sub
>
> Private Sub UserForm_Terminate()
> MsgBox "I am not getting here"
> End Sub
>

Hi Peter,

That makes sense.
However, the same problem works fine if the Userform is modal. I
suspect it could be some other issue wrt Userforms

I am also curious as to how you circumvented the problem of the global
variables being reset in the related issue you mentioned.

Thanks,
Jayant

> Hi Peter,
>
> That makes sense.
> However, the same problem works fine if the Userform is modal. I
> suspect it could be some other issue wrt Userforms
>
> I am also curious as to how you circumvented the problem of the global
> variables being reset in the related issue you mentioned.
>
> Thanks,
> Jayant

Yes it's strange it works OK if the Userform is modal, I would have expected
the opposite and more likely to work with a modeless form.

However I'm curious as to why it appears to work for Bob. With me the new
button gets added then all code and the form terminates, leaving a memory
leak. Maybe it's a version thing, I'm using xl2K.

I joined mid way in the thread I referred to, which related to adding
ActiveX controls to a worksheet, and in particular the Activesheet (though
not from code in a userform). I looked from several angles and was
surprised at what would and would not work. In other words adding new
controls doesn't necessarily recomiple the project (or at least not the
entire project), even when adding controls on the active sheet. But when
things failed I didn't circumvent the problem of globals being reset.

I posted back some of these "mysteries" and Stephen Bullen replied thus:

"Or to put it another way - even if I did find out why it works in some
cases and not others, I still wouldn't trust it in a production app."

For your immediate needs I would suggest adding new Forms' controls instead
of new ActiveX's.

Regards,
Peter T

Hi Jayant,

I am not sure I completely follow this.

I created a form and used your code, and I created buttons quite happily.
When I closed the form I got a message saying 'I am not getting here'.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"jjk" <jayantjk@gmail.com> wrote in message
news:1117041944.607780.21850@g44g2000cwa.googlegroups.com...
> Hi,
>
> I have a form that among other things tries to add OLEObject controls
> to the active sheet.
>
> The issue is that the userform disappears if there is a transaction
> where a control needs to be added on the worksheet. No exit event
> handlers (Deactivate, Terminate, Error) are fired when this happens.
> If there is no necessity to add an OLEObject then the form works fine.
> I tested it by commenting the OLEObject.add call. It worked fine.
> I also inserted several debug messages and all procedures execute till
> completion. After which the modal form disappear.
> If the form is modal then it works fine.
>
> I am including a skeleton code below. Working code that simulates the
> problem.
>
> This thing is really stumping me.
> Thanks,
> Jayant
>
>
> Worksheet Button click that brings up the form (UF)
> --------------------------------------------------
> Private Sub cmd_Click()
> UF.startLine = 10
> UF.Show (vbModeless)
> End Sub
>
> Userform UF code
> ----------------
> Public startLine As Long
>
> Private Sub UserForm_Activate()
> ll.Caption = "Add at Line: " & startLine
> End Sub
>
> Private Sub cmdAdd_Click()
>
> Dim sht As Worksheet
> Dim o As OLEObject
> Dim rng As Range
>
> Set sht = ActiveSheet
> Set rng = sht.Range("A" & startLine)
>
>
> Set o =sht.OLEObjects.Add(classtype:="Forms.CommandButton.1", _
> Top:=rng.Top, Left:=rng.Left, Width:=rng.Width, _
> Height:=rng.Height)
> o.Name = "tempcmd_" & startLine
>
> startLine = startLine + 1
> ll.Caption = "Add at Line: " & startLine
>
> End Sub
>
> Private Sub UserForm_Deactivate()
> MsgBox "I am not getting here"
> End Sub
>
> Private Sub UserForm_Error(ByVal Number As Integer, ByVal Description
> As MSForms.ReturnString, ByVal SCode As Long, ByVal Source As String,
> ByVal HelpFile As String, ByVal HelpContext As Long, ByVal
> CancelDisplay As MSForms.ReturnBoolean)
> MsgBox "I am not getting here"
> End Sub
>
> Private Sub UserForm_Terminate()
> MsgBox "I am not getting here"
> End Sub
>

Hi Bob,

I mailed the test workbook i am using to your email address.

Is the form you are bringing up modal or modeless?

My issue is that the code works fine if the form is modal.
But the form disappears on addition of any controls to the active sheet
when it is modeless.
You can try adding multiple objects in one iteration.
The effect is that it adds all the controls of the iteration, runs fine
to completion and then disappears.
It does not fire any exiting event.

I dont know if it is a version specific problem.
Mine is excel 2002 version 10.2614.2625

Thanks for your help.

Regards,
Jayant