CODEWITHSUNDEEP
excelvba
Vince
Vince

Reputation: 557

OLEObjects OptionButtons event handling

I have a group of OLEObjects OptionButtons but i've realized than i can't handle on change event when one Optionbutton is activated to trigger a Sub or Function like Form Objects. I need to use OLEObjets in the project, OLEObject are in a sheet. I need to get which OptionButton has activated and trigger an action

Please what would be the way to go?

enter image description here

Upvotes: 1

Views: 185

Answers (1)

EvR
EvR

Reputation: 3498

Add a classmodule, name it OptionClass, put code below in Class:

Public WithEvents Opt As MSForms.OptionButton

Private Sub Opt_Click()
'change will trigger multiple optionbuttons
    MsgBox Opt.Name
End Sub

In a module:

Dim OptionButtons() As New OptionClass 'better to do this in 2 seperate lines

Sub PutOn(Sht As Object)
    Dim Ctl As OLEObject, i As Long

    For Each Ctl In Sht.OLEObjects
    
         If TypeName(Ctl.Object) = "OptionButton" Then
               i = i + 1
                ReDim Preserve OptionButtons(1 To i)
                Set OptionButtons(i).Opt = Ctl.Object
         End If
    Next Ctl

End Sub
Sub start()
  PutOn Sheet2
End Sub

Adjust to your needs

Upvotes: 2

Related Questions