Add Userform caption value to specific range

[redspanna]

Hi all

I have a simple Userform that contains 3 Option buttons, each one's "caption" is changed when the user form is opened to show the values held in 3 cells. These are dates.

How do I add the Caption's value if selected to Sheet1 Range(A23) on closing the userform by clicking on a command button

For example if 25/02/2022 is selected this date will be copied and pasted into Sheet1 Range(A23

 

[Domenic]

Since your userform only contains 3 option buttons, try the following code . . .

With Worksheets("Sheet1").Range("A23")
    If Me.OptionButton1.Value Then
        .Value = Me.OptionButton1.Caption
    ElseIf Me.OptionButton2.Value Then
        .Value = Me.OptionButton2.Caption
    ElseIf Me.OptionButton3.Value Then
        .Value = Me.OptionButton3.Caption
    End If
End With

Change the name of the option buttons accordingly.

Hope this helps!

 

[Micron]

maybe

Dim ctl As control

For Each ctl In UserForm1.Controls
   If TypeName(ctl) = "OptionButton" Then
      If ctl = -1 Then
         MsgBox ctl.Caption
         Sheets("Sheet1").Range("A23") = ctl.Caption
         Exit Sub
      End If
   End If
Next

I guess you could put that on form close event or a button click event - not sure what event you were looking for. Comment out or remove the msgbox line when/if you get it working.

 

[redspanna]

Since your userform only contains 3 option buttons, try the following code . . .

With Worksheets("Sheet1").Range("A23")
    If Me.OptionButton1.Value Then
        .Value = Me.OptionButton1.Caption
    ElseIf Me.OptionButton2.Value Then
        .Value = Me.OptionButton2.Caption
    ElseIf Me.OptionButton3.Value Then
        .Value = Me.OptionButton3.Caption
    End If
End With

Change the name of the option buttons accordingly.

Hope this helps!

Thats great - many thanks !