PDA

View Full Version : [SOLVED] How to get name of Option Button created dynamically



DaveGib
09-28-2019, 07:36 AM
Hi,
Can someone please help me or point me in the right direction?
I have a list of Countries which can change, either the names or in number.
I need to perform a task on a selected country, but on only one, so i opted for using an Option Button.
I don't know before hand how many countries are in the list, so I have tried creating a userform that reads the country list and creates an option button for each country and puts its name in the caption, which appears to all be okay, but when i try to identify the button that has been selected i get a Variable Not Defined error, when i click on my proceed button.
The Option Buttons are all created okay, with their respective captions.
As per the code below, i first tried naming each button, i then commented the naming part out and tried to identify it as OptionButton1, OptionButton2 etc. but still get the error.
Can someone please tell me where or what i am doing wrong? - i need to be able to identify which button was selected.
Many thanks!!!!


Private Sub Userform_Initialize()


Dim Num As Long ' Number of Countries


Num = Sheet8.Range("A36").End(xlDown).Row ' Country List Range


Dim i As Long


'** Add Option Buttons


Dim opB1 As Control
Dim j As Long
Dim cn As String ' Country Name
Dim t As Long ' Top of OPtion Button


t = 119 ' top of First Option Button
j = 36 ' Start Row in Lists tab to loop through


For i = 1 To Num - 35
Set opB1 = Controls.Add("Forms.OptionButton.1")
cn = Sheet8.Cells(j, 1).Value
With opB1
.Caption = cn
' .Name = "obCountry" & i ' Commented out
.Height = 18
.Width = 120
.Left = 130
.Top = t
.Font.Size = 12
End With
t = t + 19
j = j + 1
Next i


End Sub


And in the CBProceed module it errors out and the first OptionButton1 is highlighted blue ( previously obcountry1) the obAll button has been manually put on the form.



Private Sub CBPtoceed_Click()


Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual


If obAll = False And OptionButton1 = False And OptionButton2 = False And obcountry3 = False And _
obcountry4 = False And obcountry5 = False And obcountry6 = False And obcountry7 = False And _
obcountry8 = False Then
MsgBox "Please select at least ONE Option!!>>>", vbOKOnly, " PLEASE SELECT AN OPTION!!!"
Exit Sub
End If

snb
09-28-2019, 08:51 AM
Use a listbox instead, designed in design mode.


Private sub Userform_initialize()
listbox1.list=Sheet8.Range("A36:A300").Specialcells(2).value
End Sub

SamT
09-28-2019, 09:42 AM
Dim CountryObjButs as Collection 'add to code as Module level variable




cn = Sheet8.Cells(j, 1).Value
With opB1
.Caption = cn
' .Name = "obCountry" & i ' Commented out
.Height = 18
.Width = 120
.Left = 130
.Top = t
.Font.Size = 12
End With
CountryObjButs.Add opB1 'add to code inside loop


Private Sub CBPtoceed_Click()
Dim AButtonIsSelected As Boolean
Dim Ctrl As Control

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

For Each Ctrl in CountryObjButs
If IsNull(Ctrl) Then Goto CtrlNext 'Due to Quirks of Null math
AButtonIsSelected = AButtonIsSelected + Ctrl.Value
CtrlNext:
Next

If Not AButtonIsSelected Then
MsgBox "Please select at least ONE Option!!>>>", vbOKOnly, " PLEASE SELECT AN OPTION!!!"
Exit Sub
End If

Paul_Hossler
09-28-2019, 10:33 AM
Following your approach, and stealing a little from Sam

You could use a Options class With Events, but this is a little more straight forward




Option Explicit


Dim aryOB() As msForms.OptionButton
Dim r As Range


Private Sub Userform_Initialize()
Dim t As Long ' Top of OPtion Button
Dim r1 As Range

With Sheet8
Set r = Range(.Range("A36"), .Range("A36").End(xlDown))

ReDim aryOB(36 To 36 + r.Rows.Count - 1)
End With

t = 119 ' top of First Option Button

For Each r1 In r.Cells
Set aryOB(r1.Row) = Controls.Add("Forms.OptionButton.1")
With aryOB(r1.Row)
.Caption = r1.Value
.Height = 18
.Width = 120
.Left = 130
.Top = t
.Font.Size = 12
End With

t = t + 19
Next


End Sub




Private Sub CBPtoceed_Click()
Dim i As Long


Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual


For i = LBound(aryOB) To UBound(aryOB)
If aryOB(i) Then
MsgBox "Selected " & aryOB(i).Caption
Exit Sub
End If
Next i


MsgBox "Nothing Selected"
End Sub

snb
09-28-2019, 01:36 PM
This should be sufficient:


Private Sub Userform_Initialize()
sn=Range("A36:A300").specialcells(2)

for j=1 to ubound(sn)
with Controls.Add("Forms.OptionButton.1", "C_" & j)
.Caption = sn(j,1)
.Height = 18
.Width = 120
.Left = 130
.Top = 120+ j*20
End With
Next
End Sub

Private Sub CBPtoceed_Click()
For j=1 to 35
if Me("C_" & j) Then exit for
Next
MsgBox iif(j>35,"Nothing Selected",Me("C_" & j).caption)
End Sub

DaveGib
09-28-2019, 10:34 PM
SamT, - Good Morning!!.......... thank you soo much for your response, I will give it a try, I learn something new every day!!
Thanks again!!!

DaveGib
09-28-2019, 10:36 PM
Paul, - i thank you for your time and effort!!, and thank you also for including the module for me!! - very grateful!! ... i see that i have 3 amazing responses thank you, ..thank you,....... thank you!!!

DaveGib
09-28-2019, 10:40 PM
snb!! ... thanks for the very concise reply!!!, as i said to SamT, I am learning more each day, and here are three amazing results, just showing that there is always more than one way to skin a cat! ( I hope that is not being offensive to any animal lovers, - just an expression!)
thank you very much!!