Combobox populating based on Option Button

[Todd Huttenstine]

On UserForm3 I have 1 combobox(ComboBox3) and then 3
Option buttons(Option button 1, 2, and 3). I need it to
where when the user clicks on OptionButton1 and then
clicks the dropdown on ComboBox3, it will list everything
in range A3:A52 on Sheet2. Also, If the user clicks
OptionButton2, and then clicks the dropdown on ComboBox3,
it will list everything in range B3:B52 on Sheet2. And
last, if the user clicks OptionButton3, and then clicks
the dropdown on ComboBox3, it will list everything in
range C3:C52 on Sheet2.

What is the code I need to achieve this?

I need for the code to ignore any empty cells in any of
the ranges so that there are no blank places in the
dropdown box(ComboBox3).

Thanks to anyone who helps.


Todd Hutenstine

 

[Bob Phillips]

Hi Todd,

We are seeing a lot of you in here <vbg>.

I think this will do what you want

Option Explicit

Dim cLastRow As Long
Dim i As Long

Private Sub OptionButton1_Click()
cLastRow = Worksheets("Sheet2").Cells(Rows.Count, "A").End(xlUp).Row
With ComboBox1
.Clear
For i = 3 To cLastRow
If Cells(i, "A") <> "" Then
.AddItem Worksheets("Sheet2").Cells(i, "A").Value
End If
Next i
.ListIndex = 0
End With
End Sub

Private Sub OptionButton2_Click()
cLastRow = Worksheets("Sheet2").Cells(Rows.Count, "B").End(xlUp).Row
With ComboBox1
.Clear
For i = 3 To cLastRow
If Cells(i, "") <> "" Then
.AddItem Worksheets("Sheet2").Cells(i, "B").Value
End If
Next i
.ListIndex = 0
End With
End Sub

Private Sub OptionButton3_Click()
cLastRow = Worksheets("Sheet2").Cells(Rows.Count, "C").End(xlUp).Row
With ComboBox1
.Clear
For i = 3 To cLastRow
If Cells(i, "") <> "" Then
.AddItem Worksheets("Sheet2").Cells(i, "C").Value
End If
Next i
.ListIndex = 0
End With
End Sub

Private Sub UserForm_Activate()
OptionButton1.Value = True
End Sub






--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Todd Huttenstine]

Where do I put these codes? I just doubleclicked on the
form and put it there. I keep getting a debug error when
I click the button to load the form now.

-----Original Message-----
Hi Todd,

We are seeing a lot of you in here <vbg>.

I think this will do what you want

Option Explicit

Dim cLastRow As Long
Dim i As Long

Private Sub OptionButton1_Click()
cLastRow = Worksheets("Sheet2").Cells

(Rows.Count, "A").End(xlUp).Row

With ComboBox1
.Clear
For i = 3 To cLastRow
If Cells(i, "A") <> "" Then
.AddItem Worksheets("Sheet2").Cells (i, "A").Value
End If
Next i
.ListIndex = 0
End With
End Sub

Private Sub OptionButton2_Click()
cLastRow = Worksheets("Sheet2").Cells

(Rows.Count, "B").End(xlUp).Row

With ComboBox1
.Clear
For i = 3 To cLastRow
If Cells(i, "") <> "" Then
.AddItem Worksheets("Sheet2").Cells (i, "B").Value
End If
Next i
.ListIndex = 0
End With
End Sub

Private Sub OptionButton3_Click()
cLastRow = Worksheets("Sheet2").Cells

(Rows.Count, "C").End(xlUp).Row

 

[Bob Phillips]

Todd,

It should be then form class module.

What do you mean by '.. I click the button to load the form now'.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Todd Huttenstine]

Well when I click the commandbutton to load Userform3.
Userform3 is the form that contains the combobox and
OptionButtons.

I double clicked userform3 and paste the code in there you
gave me. The first problem is the Option Explicit. I put
the option explicit code at the very top. Then I try to
run the code. When I run the code I get a debug screen.
When I click the debug button the line
" cLastRow = Worksheets("Sheet2").Cells
(Rows.Count, "A").End(xlUp).Row"

is highlighted in yellow. I dont know what to do from
here.

 

[Todd Huttenstine]

Actually I had to make these minor changes to get the code
to work. For some reason I had change the Worksheets
("Sheet2") to the Worksheets(2) format instead. I dont
know why I had to, but it worked when I changed it.

Thank you for your help.

Private Sub OptionButton1_Click()
cLastRow = Worksheets(2).Cells(Rows.Count, "A").End
(xlUp).Row
With ComboBox1
.Clear
For i = 3 To cLastRow
If Cells(i, "A") <> "" Then
.AddItem Worksheets(2).Cells(i, "A").Value
End If
Next i
.ListIndex = 0
End With
End Sub

Private Sub OptionButton2_Click()
cLastRow = Worksheets(2).Cells(Rows.Count, "B").End
(xlUp).Row
With ComboBox1
.Clear
For i = 3 To cLastRow
If Cells(i, "B") <> "" Then
.AddItem Worksheets(2).Cells(i, "B").Value
End If
Next i
.ListIndex = 0
End With
End Sub

Private Sub OptionButton3_Click()
cLastRow = Worksheets(2).Cells(Rows.Count, "C").End
(xlUp).Row
With ComboBox1
.Clear
For i = 3 To cLastRow
If Cells(i, "C") <> "" Then
.AddItem Worksheets(2).Cells(i, "C").Value
End If
Next i
.ListIndex = 0
End With
End Sub

 

[Tom Ogilvy]

There is a typo in the code for optionbutton2 and 3

Cells(i,"") <> "" should be with a B and C respectively in the empty
double quotes.


Option Explicit

Dim cLastRow As Long
Dim i As Long

Private Sub OptionButton1_Click()
cLastRow = Worksheets("Sheet2").Cells(Rows.Count, "A").End(xlUp).Row
With ComboBox1
.Clear
For i = 3 To cLastRow
If Cells(i, "A") <> "" Then
.AddItem Worksheets("Sheet2").Cells(i, "A").Value
End If
Next i
.ListIndex = 0
End With
End Sub

Private Sub OptionButton2_Click()
cLastRow = Worksheets("Sheet2").Cells(Rows.Count, "B").End(xlUp).Row
With ComboBox1
.Clear
For i = 3 To cLastRow
If Cells(i, "B") <> "" Then
.AddItem Worksheets("Sheet2").Cells(i, "B").Value
End If
Next i
.ListIndex = 0
End With
End Sub

Private Sub OptionButton3_Click()
cLastRow = Worksheets("Sheet2").Cells(Rows.Count, "C").End(xlUp).Row
With ComboBox1
.Clear
For i = 3 To cLastRow
If Cells(i, "C") <> "" Then
.AddItem Worksheets("Sheet2").Cells(i, "C").Value
End If
Next i
.ListIndex = 0
End With
End Sub

Private Sub UserForm_Activate()
OptionButton1.Value = True
End Sub

 

[Bob Phillips]

Todd,

So you're up and running now ... that's good.

Bob