Multiple OptionButtons - Cell updated with selection

[Watchdawg]

Good morning all. I hope that I'm putting this in the correct place, I've looked and some of the answers I've found answer part of the issue but not all. I have a userform. When the user double-clicks the "Vendor" cell in the workbook, it pulls a userform with 28 vendors attached to option buttons. There can be only one vendor per cell, which is exactly how I need it. My problem is when an optionbutton is selected, how do I make it automatically populate the ActiveCell with the .Caption from the option button and close the UserForm? I know that I can manually attach code to each and every optionButton to perform this action, but that's amazingly tedious and very messy for code.
Here's the code that I can attach to each button (although this is probably pointless for the folks here)

Private Sub OptionButton1_Click()
    If OptionButton1.Value = True Then
        ActiveCell.Value = OptionButton1.Caption
    End If
End Sub

 

[Fluff]

Rather than having 28 option buttons, why not use a listbox, or combobox?

 

[Watchdawg]

That was my original thought as well, Fluff. That said, this is what the boss prefers so this is what I'm trying to make work! LOL

 

[Fluff]

Would you settle for a command button to push the data to the sheet?

 

[Watchdawg]

Would you settle for a command button to push the data to the sheet?

Absolutely!

 

[Fluff]

In that case try

Private Sub CommandButton1_Click()
    Dim Ctrl As MSForms.Control
    
    For Each Ctrl In Me.Controls
        If TypeOf Ctrl Is MSForms.OptionButton Then
            If Ctrl Then ActiveCell.Value = Ctrl.Caption
        End If
    Next Ctrl
End Sub

 

[Watchdawg]

Perfect!! Thank you so much Fluff!

 

[Fluff]

You're welcome & thanks for the feedback.

 

[Watchdawg]

Of course, I love it when I come here, I'm always learning a new / better way to do something with VBA!

 

[Fluff]

Another way of doing it, without the command button is:-
Create a new class module & call it "ClsOptBtn" then add this code

Option Explicit
Public WithEvents Optbtn As MSForms.OptionButton
Private Sub OptBtn_Click()
    ActiveCell.Value = Optbtn.Caption
End Sub

Then in the Userform module put

Option Explicit
Private OptBtnEvent As Collection

Private Sub UserForm_Initialize()
    Dim Ctrl As MSForms.Control
    Dim Obtn As ClsOptBtn
   
    Set OptBtnEvent = New Collection
    For Each Ctrl In Me.Controls
        If TypeOf Ctrl Is MSForms.OptionButton Then
            Set Obtn = New ClsOptBtn
            Set Obtn.Optbtn = Ctrl
            OptBtnEvent.Add Obtn
        End If
    Next
End Sub

Ensuring the two lines in blue are at the very top of the module, before any code