UserForm OptionButton Click

alselten · Jan 1, 2015

Hi,

I'm working on a userform that creates option buttons from a range.
Now I would like to set a variable, base on the caption, when clicking a option button.

I tried many things, but noting seems to work for me.
Can anyone help me please?

My code for creating the option buttons:

Private Sub UserForm_Activate()

Cust_Edit.Visible = False
Dim SearchString As String
Dim newbutton As msforms.OptionButton
SearchString = Sheets("Invullijst").Range("I3").Value
Last_Address_Row = Worksheets("Adressen").Range("A65536").End(xlUp).Row
i = 0
For Each sCell In Worksheets("Adressen").Range("A1:A" & Last_Address_Row).Cells
If sCell Like "*" & UCase(SearchString) & "*" Or Cell Like "*" & LCase(SearchString) & "*" Or sCell Like "*" & StrConv(SearchString, vbProperCase) & "*" Then

i = i + 1

Set newbutton = Me.Controls.Add("Forms.OptionButton.1")
With newbutton
.Name = "Opt" & i
.Top = 10 + (20 * i)
.Left = 10
.Width = 200
.Caption = sCell.Value
.GroupName = "Custs"
End With
End If
Next sCell

End Sub

Andrew Poulsom · Jan 1, 2015

Welcome to MrExcel.

Maybe you can adapt the VBA code here:

Spreadsheet Page Excel Tips: Creating A Userform Programmatically

alselten · Jan 1, 2015

Hi Andrew,
Thanks for the tip, but the building of the UserForm options is already done by the script I pasted.
The point is now how to add a event on click for each option.

Andrew Poulsom said:
Welcome to MrExcel.

Maybe you can adapt the VBA code here:

Spreadsheet Page Excel Tips: Creating A Userform Programmatically

GTO · Jan 1, 2015

If you REM this line:

' Delete the form ' ThisWorkbook.VBProject.VBComponents.Remove VBComponent:=TempForm 

...in Mr. Walkenbach's code (in GetOption()), you can see the event handler code written to the created form's module:

Option Explicit Sub CommandButton1_Click() GETOPTION_RET_VAL = False Unload Me End Sub Sub CommandButton2_Click() Dim ctl GETOPTION_RET_VAL = False For Each ctl In Me.Controls If ctl.Tag <> "" Then If ctl Then GETOPTION_RET_VAL = ctl.Tag Next ctl Unload Me End Sub

If you do need to update a variable upon choosing an option (rather than upon the command button), here is an example:

In a new blank workbook...

A userform (I named: frmTest) with one commandbutton default named.
A Class module default named.

In the Class Module:

Option Explicit Private objForm As Object 'Declare the form as generic Object Private WithEvents objOptBttn As MSForms.OptionButton Private strBttnTag As String Public Property Set ParentForm(pf As Object) Set objForm = pf End Property Public Property Get ParentForm() As Object Set ParentForm = objForm End Property Public Property Set OptBttn(ob As MSForms.OptionButton) Set objOptBttn = ob End Property Public Property Get OptBttn() As MSForms.OptionButton Set OptBttn = objOptBttn End Property Public Property Let BttnTag(bt As String) strBttnTag = bt End Property Public Property Get BttnTag() As String BttnTag = strBttnTag End Property Private Sub objOptBttn_Click() '// Pass the value back to the form's variable// ParentForm.OptSelected = BttnTag ParentForm.Caption = "Current selected: " & BttnTag End Sub

In frmTest's Module:

Option Explicit Private strOptSelected As String '// Declare as array and redim when we know how many buttons // Private cls() As Class1 Public Property Let OptSelected(os As String) strOptSelected = os End Property Public Property Get OptSelected() As String OptSelected = strOptSelected End Property Private Sub CommandButton1_Click() If Len(OptSelected) Then Me.CommandButton1.Caption = OptSelected Else Me.CommandButton1.Caption = "empty" End If End Sub Private Sub UserForm_Initialize() Dim Bttn As MSForms.OptionButton Dim n As Long Dim lLastRow As Long With ThisWorkbook.Worksheets("Adressen") '// Use .Rows.Count so that when converted to Excel 2007 or later...// lLastRow = .Cells(.Rows.Count, 1).End(xlUp).Row End With '// Size the array // ReDim cls(1 To lLastRow) For n = 1 To lLastRow Set Bttn = Me.Controls.Add("Forms.OptionButton.1", "opt" & n, True) With Bttn .Top = ((n * 18) + 6) - 18 .Left = 6 .Width = 200 .Caption = ThisWorkbook.Worksheets("Adressen").Cells(n, 1).Value .Tag = ThisWorkbook.Worksheets("Adressen").Cells(n, 1).Value .GroupName = "Custs" Set cls

= New Class1 With cls

Set .ParentForm = Me Set .OptBttn = Bttn .BttnTag = Bttn.Tag End With End With Next End Sub

Hope that helps,

Mark

GTO · Jan 1, 2015

ACK!

I forgot to mention: I would use the form's Initialize event instead of the Activate event.

Mark

alselten · Jan 5, 2015

Hi Mark,

Thanks for the code, it works like a charm!

GTO said:
ACK!

I forgot to mention: I would use the form's Initialize event instead of the Activate event.

Mark

GTO · Jan 5, 2015

Thanks for the feedback and you are most welcome

UserForm OptionButton Click

alselten

New Member

Excel Facts

Andrew Poulsom

MrExcel MVP

alselten

New Member

GTO

MrExcel MVP

GTO

MrExcel MVP

alselten

New Member

GTO

MrExcel MVP

Similar threads

Forum statistics

Share this page

UserForm OptionButton Click

alselten

New Member

Excel Facts

Andrew Poulsom

MrExcel MVP

alselten

New Member

GTO

MrExcel MVP

GTO

MrExcel MVP

alselten

New Member

GTO

MrExcel MVP

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock