How to get name of Option Button created dynamically

[DaveGib]

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]

Use a listbox instead, designed in design mode.

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

[SamT]

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]

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]

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]

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]

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]

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!!