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" wrote in message
ups.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.CommandButto n.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