Option group and Combobox
 

I have a userform with 2 option checks and a combo box.
each option check when checked takes put the focus of the
userform onto a sheet within an excel workbook.
each sheet has multiple columns filled with data.
The first column in each is the reference column.

When I click on one of the options how do I get the combo
box to display in it's dropdown list the references in
the fist column of the selected sheet?

The sheets are not visable to the users so the combo box
must display the reference from the correct sheet.

This has really been bugging me....
Any help would be great.
Thanks
Andy

Option group and Combobox
 

Private Sub OptionButton1_Click()
if optionbutton1.Value Then
With Worksheets("Sheet1")
set rng = .Range(.Cells(1,1),.Cells(rows.count,1).End(xlup))
End with
Combobox1.rowsource = ""
Combobox1.List = rng.Value
end if
End Sub


Private Sub OptionButton2_Click()
if optionbutton2.Value Then
With Worksheets("Sheet2")
set rng = .Range(.Cells(1,1),.Cells(rows.count,1).End(xlup))
End with
Combobox1.rowsource = ""
Combobox1.List = rng.Value
end if
End Sub


If they are really checkboxes, just change the names in the routines.

--
Regards,
Tom Ogilvy


Andy wrote in message
...
I have a userform with 2 option checks and a combo box.
each option check when checked takes put the focus of the
userform onto a sheet within an excel workbook.
each sheet has multiple columns filled with data.
The first column in each is the reference column.

When I click on one of the options how do I get the combo
box to display in it's dropdown list the references in
the fist column of the selected sheet?

The sheets are not visable to the users so the combo box
must display the reference from the correct sheet.

This has really been bugging me....
Any help would be great.
Thanks
Andy

Option group and Combobox
 

Thanks for the help.

Andy
-----Original Message-----
Private Sub OptionButton1_Click()
if optionbutton1.Value Then
With Worksheets("Sheet1")
set rng = .Range(.Cells(1,1),.Cells(rows.count,1).End
(xlup))
End with
Combobox1.rowsource = ""
Combobox1.List = rng.Value
end if
End Sub


Private Sub OptionButton2_Click()
if optionbutton2.Value Then
With Worksheets("Sheet2")
set rng = .Range(.Cells(1,1),.Cells(rows.count,1).End
(xlup))
End with
Combobox1.rowsource = ""
Combobox1.List = rng.Value
end if
End Sub


If they are really checkboxes, just change the names in
the routines.

--
Regards,
Tom Ogilvy


Andy wrote in
message
...
I have a userform with 2 option checks and a combo box.
each option check when checked takes put the focus of
the
userform onto a sheet within an excel workbook.
each sheet has multiple columns filled with data.
The first column in each is the reference column.

When I click on one of the options how do I get the
combo
box to display in it's dropdown list the references in
the fist column of the selected sheet?

The sheets are not visable to the users so the combo box
must display the reference from the correct sheet.

This has really been bugging me....
Any help would be great.
Thanks
Andy


.