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