VBA: Dynamically Create Active X Frame and Yes / No Radio Button

[quicksandNAV]

Hello,

The following works correctly / as expected:

Form Controls / Group Box / Radio Button Method:

            ActiveSheet.GroupBoxes.Add(Range("B" & (ThisRow + 1)).Left, Range("B" & (ThisRow + 1)).Top, Range("B" & (ThisRow + 1)).Width, Range("B" & (ThisRow + 1)).Height).Select
            Selection.Characters.Text = ""
            ActiveSheet.OptionButtons.Add(Range("B" & (ThisRow + 1)).Left + 1, Range("B" & (ThisRow + 1)).Top + 1, 72, 10).Select
            Selection.Characters.Text = "No Issues Found"
            Selection.Value = True
            ActiveSheet.OptionButtons.Add(Range("B" & (ThisRow + 1)).Left + 1, Range("B" & (ThisRow + 1)).Top + 15, 72, 10).Select
            Selection.Characters.Text = "Issues Found"

I'll include the whole block of code at the bottom. Basically, I'm looking to convert the above section to using Active X Controls so that it will be possible to adjust the size of the radio button and the button text.

             ActiveSheet.OLEObjects.Add(ClassType:="Forms.Frame.1", Link:=False, DisplayAsIcon:=False, Left:=Range("B" & (ThisRow + 1)).Left, Top:=Range("B" & (ThisRow + 1)).Top, Width:=Range("B" & (ThisRow + 1)).Width, Height:=Range("B" & (ThisRow + 1)).Height).Select
             'Selection.Caption = ""

That does successfully create the frame, but the attempt to set the caption to empty fails (cannot reference that way). I've tried recording Macro to catch; no code is shown for updating the caption text (entirely possible I'm not doing something right to catch it). Assuming the frame caption can be removed, it'd be good to add two radio buttons to the frame, and set / adjust their properties (font / size, default one of them to already clicked, etc), similar to the group boxes method that works.

Here is the whole code body:

Public Sub Worksheet_Change(ByVal Target As Excel.Range)
    'If Intersect(Target, Range(INDIRECT("C5:C"&ROWS(C:C))) Is Nothing Then Exit Sub
    If Target.Column = 4 Then
        ThisRow = Target.Row
        If Target.Value <> "" Then
            ' We will be changing cells from an event, so disable events until done
            Application.EnableEvents = False
            
            ActiveSheet.Unprotect Password:="password"
            'Range("E" & ThisRow).Value = Now()
            Range("F" & ThisRow).Value = Format(Now, "mm-dd-yy")
            For Each cell In ActiveSheet.Range("A" & ThisRow & ":G" & ThisRow)
               cell.Locked = True
            Next cell
            
            ' Set default values for the next row
            'Range("A" & (ThisRow + 1)).Value = "N/A"
            'Range("B" & (ThisRow + 1)).CellControl.SetCheckbox
            
            ActiveSheet.GroupBoxes.Add(Range("B" & (ThisRow + 1)).Left, Range("B" & (ThisRow + 1)).Top, Range("B" & (ThisRow + 1)).Width, Range("B" & (ThisRow + 1)).Height).Select
            Selection.Characters.Text = ""
            ActiveSheet.OptionButtons.Add(Range("B" & (ThisRow + 1)).Left + 1, Range("B" & (ThisRow + 1)).Top + 1, 72, 10).Select
            Selection.Characters.Text = "No Issues Found"
            Selection.Value = True
            ActiveSheet.OptionButtons.Add(Range("B" & (ThisRow + 1)).Left + 1, Range("B" & (ThisRow + 1)).Top + 15, 72, 10).Select
            Selection.Characters.Text = "Issues Found"
            
            'With ActiveSheet.OLEObjects.Add(ClassType:="Forms.Frame.1", Link:=False, DisplayAsIcon:=False, Left:=Range("B" & (ThisRow + 1)).Left, Top:=Range("B" & (ThisRow + 1)).Top, Width:=Range("B" & (ThisRow + 1)).Width, Height:=Range("B" & (ThisRow + 1)).Height)
            '    Me.Caption = ""
            'End With
            
            Range("C" & (ThisRow + 1)).CellControl.SetCheckbox
            
            ' Re-protect the sheet
            ActiveSheet.Protect Password:="password"
            
            ' Re-enable events
            Application.EnableEvents = True
            
            'Save the sheet
            ThisWorkbook.Save
            
        End If
    End If
End Sub

 

[Micron]

Interestingly, it seems the frame isn't associated with the button groups;

You can place container objects (frames and subform controls in Access) then place other objects inside the frame improperly while in design mode so that they are not "children" of the parent control (container object). Maybe that's what's happening with this code method of placing the option buttons. I suspect that because upon re-opening the wb, the buttons are not visible unless I enter design mode. So I moved a frame and the buttons are underneath. That tells me that they are not part of the frame, they are actually located below the frame because the frame came first in the z order. If they were "children" of the frame, they would have their own group name from what I read here. Not sure if that makes sense, or if this stuff works the same in Excel as it does in Access

 

[Micron]

Apparently it is as I'm saying according to this, which I tried.
top left - frame removed and turned off design mode. Btns visible because frame removed, the rest that were added via that code are not visible..
center and lower right - btns added using linked method. Their grouping is based on the frame.

I suspect the thing to do would be to try recording that as a macro and see what comes of it. Or you need to research to see how to add buttons to a frame via vba and make sure the answers provide the proper method. Or ditch the frame in your code as it doesn't seem to be needed since it's not providing any grouping. Or if it's eye candy you're after, do something else such as background colour or shading of the row/cells.

 

[Domenic]

To create a frame, and then add two option buttons within the frame itself try the following code. You'll need to adjust the properties, such as width, height, etc, as desired.

            Dim oleFrame As OLEObject
            Dim optRadio1 As msforms.OptionButton
            Dim optRadio2 As msforms.OptionButton
            Dim topPos As Single
           
            topPos = 2
           
            Set oleFrame = ActiveSheet.OLEObjects.Add(ClassType:="Forms.Frame.1", Link:=False, DisplayAsIcon:=False, Left:=Range("B" & (ThisRow + 1)).Left, Top:=Range("B" & (ThisRow + 1)).Top, Width:=Range("B" & (ThisRow + 1)).Width, Height:=Range("B" & (ThisRow + 1)).Height)
            oleFrame.Object.Caption = ""
           
            Set optRadio1 = oleFrame.Object.Controls.Add("Forms.OptionButton.1")
            With optRadio1
                .Caption = "No Issues Found"
                .Top = topPos
            End With
           
            topPos = topPos + optRadio1.Height
           
            Set optRadio2 = oleFrame.Object.Controls.Add("Forms.OptionButton.1")
            With optRadio2
                .Caption = "Issues Found"
                .Top = topPos
            End With

            oleFrame.Activate

Hope this helps!

 

[Domenic]

By the way, if you want to manually add a control to an ActiveX frame, you would need to right-click the frame, then select Frame Object, and then select Edit. Then the Toolbox will become available so that a control can be added to the frame.