How to add a form control-optionbutton in VBA (not ActiveX-optionbutton)?

Question

I need to know how to add Form control optionbuttons to a WorkSheet by VBA. So far I've only managed to add ActiveX controls, which behave differently.

I'm building an Excel 365 form (on a WorkSheet, not a VBA UserForm) including several groups of radiobuttons/optionbuttons. I see that I can add two kinds of optionnbuttons: Form controls and ActiveX controls. Both controls can be linked to a cell, but return different results: Form controls return a number as a result ActiveX controls return TRUE or FALSE

I can add a optionbutton to my sheet using this code:

With FormSheet.OLEObjects.Add(ClassType:="Forms.Optionbutton.1")
    .Left = 10
end with

I was hoping to get Form Controls, but as far as I can see the created controls are ActiveX controls. When I add a .LinkedCell, they return Boolean values.

T C T C · Accepted Answer · 2019-02-13T23:58:03

Hmpf...

Dim OptBtn As OptionButton
Set OptBtn = ActiveSheet.OptionButtons.Add(580.5, 48.75, 72, 72)

seems to do the trick.

How to add a form control-optionbutton in VBA (not ActiveX-optionbutton)?

1 Answers