CODEWITHSUNDEEP
excelvba
T Zhang
T Zhang

Reputation: 105

How to make an OptionButton ActiveX control change back-color to transparent?

I have added a few Excel ActiveX control option button to a sheet, and I need to change back-color to transparent. I used the following code but it is not working.

Sub removecolor()
    Dim cb As OptionButton
    For Each cb In ActiveSheet.OptionButton
        cb.Interior.ColorIndex = xlNone
    Next cb
End Sub

Would you please give me a clue?

Upvotes: 4

Views: 2107

Answers (1)

41686d6564
41686d6564

Reputation: 19641

There are two points you should be aware of to make this work:

  1. To access the OptionButton controls on the sheet, you need to iterate its OLEObjects collection.

  2. An ActiveX control does not have an Interior.ColorIndex property. It does, however, have BackColor and BackStyle properties. You should be using BackStyle in this case in order to make the control transparent.

This should work:

Sub RemoveColor()
    For Each obj In ActiveSheet.OLEObjects
        If TypeName(obj.Object) = "OptionButton" Then
            obj.Object.BackStyle = fmBackStyleTransparent
        End If
    Next
End Sub

If you don't like providing the type name as a string, you can replace the condition with:

If TypeOf obj.Object Is MSForms.OptionButton Then   ' etc.

Upvotes: 2

Related Questions