Forum Discussion

GeorgieAnne's avatar
GeorgieAnne
Iron Contributor
Nov 23, 2025

Add VBA Code to an Option button dynamically

Hello Excelers,

I hope you are doing well. I am back for some more help and here is what I am trying to accomplish:

I have some code that would add x Option Buttons to a User form. x is Unknown but usually less than 15 but maybe more at times.

I need to assign a macro that would return the newly created Option Button's Caption.

 

In a Module I have:

Dim objOptBtn as MSForms.OptionButton

In a For i = 1 to x loop I have:

Set objOptBtn = uFrmChoice.controls.add("Forms.OptionButton.1","objOptBtn" & i,true)

With objOptBtn

         .Caption = i

         .snglTextWidth = Len(objOptBtn.caption)

         .width = snglTextWidth + 18

         .Left = 10

         .Top = TopLevel + 10

         .Width = 400

         .Height = 18

End With

I remember there was an .OnAction "MacroNameYouWantToRunWhenOptionButtonIsClicked" But I do not see it in the Intelisense????

 

How can I assign code to run once the user clicks on a Option Button on the UserForm and return the Caption of that Option button?

I am trying to ask the user to choose ONE option from a set of unknown options.

 

developer
excel
Macros and VBA

1 Reply

  • NikolinoDE's avatar
    NikolinoDE
    Platinum Contributor
    Dec 01, 2025

    You’re correct — UserForm OptionButtons do not have .OnAction.
    That property only exists for Worksheet controls (Form Controls).
    UserForm controls use class modules and events, not .OnAction.

     

    Below is a working, VBA solution (I hope so 😊) you can paste directly into your project.
    It dynamically creates OptionButtons on a UserForm and captures which button was clicked, returning the Caption of the selected button.

     

    Create Class Module: clsOptButton

    Insert → Class Module
    Rename it exactly:

    clsOptButton

    Paste this code:

    Option Explicit

Public WithEvents Opt As MSForms.OptionButton   ' dynamic OptionButton with events

Private Sub Opt_Click()
    ' When clicked, send the caption back to the UserForm
    uFrmChoice.SelectedOption = Opt.Caption
End Sub

     

    UserForm Code (uFrmChoice)

    Open your UserForm → paste this code into the form.

    This version creates dynamic buttons, aligns them, and stores the selected option.

    Option Explicit

Public SelectedOption As String      ' holds the chosen option
Private OptHandlers As Collection    ' stores event handler objects

Private Sub UserForm_Initialize()
    Dim i As Long, x As Long
    Dim objOptBtn As MSForms.OptionButton
    Dim handler As clsOptButton
    Dim topPos As Long
    
    x = 10  ' number of options (example) — replace with your own dynamic count

    Set OptHandlers = New Collection
    topPos = 20
    
    For i = 1 To x
    
        ' Create the OptionButton
        Set objOptBtn = Me.Controls.Add("Forms.OptionButton.1", "objOptBtn" & i, True)

        With objOptBtn
            .Caption = "Option " & i
            .Left = 20
            .Top = topPos
            .Width = 150
            .Height = 18
        End With
        
        topPos = topPos + 22
        
        ' Create event handler and bind the control to it
        Set handler = New clsOptButton
        Set handler.Opt = objOptBtn
        
        ' Must store handler to keep events alive
        OptHandlers.Add handler
    Next i
End Sub

     

    Show the form from anywhere

    In a standard module:

    Sub TestDynamicOptions()
    uFrmChoice.SelectedOption = ""      ' reset
    uFrmChoice.Show                     ' show UserForm
    
    If uFrmChoice.SelectedOption <> "" Then
        MsgBox "You selected: " & uFrmChoice.SelectedOption
    Else
        MsgBox "No option selected."
    End If
End Sub

    You can copy all 3 code pieces exactly and it will run immediately....i hope so 🙂...the codes are not testet, please backup your file first.

     

     

    Hope this helps you.

Resources