Form object in container frame on worksheet inactive unless Design Mode manually toggled

Question

I want to create ActiveX objects directly on a worksheet. I can do this programmatically.

I also want several controls grouped together with a particular background. I created them within a Frame object: i.e. the controls would be "child objects" of the frame.

The following sample code does the job:

Sub CreateFormOnSheet()
    With ActiveSheet
        ' Add the frame background:
        .OLEObjects.Add(ClassType:="Forms.Frame.1", Left:=10, Top:=10, Width:=300, Height:=300).Name = "container_frame"
        With .OLEObjects("container_frame")
            With .Object
                .Caption = "This is the frame caption"
                .BackColor = RGB(150, 0, 100)
                .BorderColor = RGB(255, 255, 255)
                .Controls.Add("Forms.CommandButton.1").Name = "MyButton"
                With .Controls("MyButton")
                    .Left = 10
                    .Top = 10
                    .Width = 100
                    .Height = 50
                    .BackColor = RGB(0, 0, 100)
                    .ForeColor = RGB(255, 255, 255)
                    .Caption = "My Button"
                    .FontName = "Arial"
                    .Font.Bold = True
                    .Font.Size = 10
                    .WordWrap = True
                End With
            End With
        End With
    End With
End Sub

The problem is: at the end of code execution, MyButton acts as if it's "locked", or disabled. The user can not click on it. There is no "button press" animation, of the sort that accompanies CommandButton objects.

Adding .Enabled = True does not fix this. It is already enabled, it just acts like it's not.

If I manually enter "Design Mode" - and then exit again - the button enables.

I found out how to programmatically enable/disable Design Mode:

Sub testEnter()
    EnterExitDesignMode True
End Sub

Sub testExit()
    EnterExitDesignMode False
End Sub

Sub EnterExitDesignMode(bEnter As Boolean)
Dim cbrs As CommandBars
Const sMsoName As String = "DesignMode"

    Set cbrs = Application.CommandBars
        If Not cbrs Is Nothing Then
        If cbrs.GetEnabledMso(sMsoName) Then
            If bEnter <> cbrs.GetPressedMso(sMsoName) Then
                cbrs.ExecuteMso sMsoName
                Stop
            End If
        End If
    End If
End Sub

... however if I add the lines:

testEnter
DoEvents
testExit

... to the end of my Sub, the problem remains. Even if it worked, that seems like a hack. I'd much rather understand what's going on here, and apply a proper solution.

Answer 1

I think this is an known issue with adding OLEObjects, workaround is to toggle between not visible and visible. In this case for your Frame. (or method mentioned in comment above)

Sub CreateFormOnSheet()
    With ActiveSheet
        ' Add the frame background:
        .OLEObjects.Add(ClassType:="Forms.Frame.1", Left:=10, Top:=10, Width:=300, Height:=300).Name = "container_frame"
        With .OLEObjects("container_frame")
            With .Object
                .Caption = "This is the frame caption"
                .BackColor = RGB(150, 0, 100)
                .BorderColor = RGB(255, 255, 255)
                .Controls.Add("Forms.CommandButton.1").Name = "MyButton"
                With .Controls("MyButton")
                    .Left = 10
                    .Top = 10
                    .Width = 100
                    .Height = 50
                    .BackColor = RGB(0, 0, 100)
                    .ForeColor = RGB(255, 255, 255)
                    .Caption = "My Button"
                    .FontName = "Arial"
                    .Font.Bold = True
                    .Font.Size = 10
                    .WordWrap = True
                 End With
            End With
            .Visible = False 'toggle the Frame
            .Visible = True
        End With
      'or Sheets(1).Activate
      'or .Activate
     End With

End Sub

See also: https://www.excelforum.com/excel-programming-vba-macros/679211-cant-enter-break-mode-at-this-time-error.html#post2073900

It is also not possible to step through with F8