Conflict bewteen OLEObjects and Userform

Question

I am trying to delete an ActiveX OLEObject from a sheet and then launch a UserForm as vbModeLess.

In the code below:

First, a sub creates a OLEObject (a Label) in sheet "mySheet" with its caption and name.

Sub creteOleo()

    Dim rng As Range
            
    Set rng = Sheets("mySheet").Range("B2:C3")
            
    With Sheets("mySheet").OLEObjects.Add(ClassType:="Forms.Label.1", _
                                            Link:=False, _
                                            DisplayAsIcon:=False, _
                                            Left:=rng.Left, _
                                            Top:=rng.Top, _
                                            Width:=rng.Width, _
                                            Height:=rng.Height)
        .Name = "myOleo" 'A name
        .Object.Font.Size = 13
        .Object.Caption = "test"
    End With
End Sub

Then a sub that deletes it, then launches a UserForm (the UserForm just contains a label, it's for testing so there is no code inside) with vbModeLess state.

Sub deleteOleo()
    Sheets("mySheet").OLEObjects("myOleo").Delete
    UserForm1.Show vbModeless
End Sub

Here is the problem, deleting a specific OLEObject then launching the UserForm deletes the OLEObject as expected but then the UserForm appears for a brief moment before disappearing.

This problem only appears when UserForm is launched in vbModeLess state, so I went for another way (hiding the object instead of deleting it) that works, but I am curious to know why VBA behaves that way.

My understanding is that since Excel executes the UserForm1.Show after finishing deleting the OLEObject, it shouldn't interfere with it. Moreover, it isn't like if I was deleting all objects, but just a specific one.

user_1140579 · Answer

Yes this is an undocumented bug (At least I never did find any documentation around it). Having said that you can achieve what you want by doing the following.

Logic

Show the form in Modal
In UserForm_Activate() event change it back to Modeless.

In a Module

Option Explicit

Sub creteOleo()
    Dim rng As Range
    
    Set rng = Sheets("mySheet").Range("B2:C3")
    
    With Sheets("mySheet").OLEObjects.Add(ClassType:="Forms.Label.1", _
                                          Link:=False, _
                                          DisplayAsIcon:=False, _
                                          Left:=rng.Left, _
                                          Top:=rng.Top, _
                                          Width:=rng.Width, _
                                          Height:=rng.Height)
                                          
        .Name = "myOleo" 'On lui donne un nom
        .Object.Font.Size = 13
        .Object.Caption = "test"
    End With
End Sub

Sub deleteOleo()
    Sheets("mySheet").OLEObjects("myOleo").Delete
    
    DoEvents
    
    Dim frm As New UserForm1
    
    frm.Show
End Sub

In the Userform Code

Option Explicit

Private Declare Function EnableWindow Lib "user32.dll" (ByVal hWnd As Long, _
ByVal fEnable As Long) As Long

Private Declare Function FindWindow Lib "user32.dll" Alias "FindWindowA" _
(ByVal lpClassName As String, ByVal lpWindowName As String) As Long

Private Declare Function GetParent Lib "user32.dll" (ByVal hWnd As Long) As Long

Private Sub UserForm_Activate()
    EnableWindow GetParent(FindWindow(vbNullString, Me.Caption)), 1
End Sub

In Action

Conflict bewteen OLEObjects and Userform

1 Answers