1

I'm using VBA to generate an Excel sheet which includes ActiveX form controls. However the documentation available for the object properties is rather sketchy. I notice that when I create, for example, an OptionButton control, the object includes a solid white border. I can manually go into Design Mode; right-click; "Format Object", then under the "Colors and Lines" tab in the dialogue box, change Fill (Automatic) to "No Fill". See the following example:

Format Object dialogue box

However I have yet to work out how to do this through code. Please see the following:

Dim sht as Sheet
Set sht = [WorkbookObject].Sheets(1)
With sht
    .OLEObjects.Add(ClassType:="Forms.OptionButton.1", Left:=4.5, Top:=34.5, Width:=105, Height:=15).Name = "RadioB_1"
    With .OLEObjects("RadioB_1").Object
        .Caption = "First Option"
        .GroupName = "ColumnFilter"
        .BackColor = RGB (128, 128, 128)
        .BackStyle = 1
    End With
    ' The above all works fine, however I can't find the correct property
    ' for the border fill. I have tried various properties for
    ' .OLEObjects("RadioB_1") and for .OLEObjects("RadioB_1").Object
    ' however I can't find the correct property.
End With

Excel's Object Browser doesn't give me much of a clue.

I have also looked at MSDN's Article on OLE Object Properties, however there doesn't appear to be anything to address what I need.

Community
  • 1
  • 1
Chris Melville
  • 1,476
  • 1
  • 14
  • 30

1 Answers1

1

Aha..! A lot more searching and I found the answer to my own question:

sht.OLEObjects("RadioB_1").ShapeRange.Fill.Transparency = 1

ShapeRange was listed on that MS page, however the name of it is misleading, and there is still nowhere in official documentation which actually lists all the properties and what they do! Anyway - I decided to post the answer to my own question for the benefit of anyone looking for this in future.

Chris Melville
  • 1,476
  • 1
  • 14
  • 30