How to get a range of values into a combo placed on USERFORM?

[Harinath]

Hi All

Please let me know some method for filling range of data to combobox on a userform from spreadsheet
The range data will be present on the spreadsheet
I filled the combo by entering individual cell values in the VBA code.Instead of doing like thi
is there any method of selecting a range of values from a sheet and put the same data into the combo in one go

Please put some light on this

Thank
Harinath

 

[Leo Heuser]

Hi Harinath

Here's one way to do it:

For data in a column:

Private Sub UserForm_Initialize()
Dim DataRange As Range
Dim DataRangeValue As Variant

Set DataRange = Sheets("Sheet1").Range("A1:A7")
DataRangeValue = DataRange.Value

Me.ComboBox1.List = DataRangeValue
End Sub


For data in a row

Private Sub UserForm_Initialize()
Dim DataRange As Range
Dim DataRangeValue As Variant

Set DataRange = Sheets("Sheet1").Range("A1:G1")
DataRangeValue = DataRange.Value

Me.ComboBox1.Column = DataRangeValue
End Sub


--
Best Regards
Leo Heuser

Followup to newsgroup only please.

Hi All,

Please let me know some method for filling range of data to combobox on a userform from spreadsheet.
The range data will be present on the spreadsheet.
I filled the combo by entering individual cell values in the VBA

code.Instead of doing like this

is there any method of selecting a range of values from a sheet and put

the same data into the combo in one go.

 

[BrianB]

Controls start numbering at zero so :-

'-------------------------------------------
'- initialise form
Private Sub UserForm_Initialize()
Set DataSheet = ThisWorkbook.Worksheets("data")
Set MyList = DataSheet.Range("FileList")
Rw = 1
While MyList.Cells(Rw, 1).Value <> ""
ComboBox1.AddItem
ComboBox1.List(Rw - 1) = MyList.Cells(Rw, 1).Value
Rw = Rw + 1
Wend
End Sub
'---------------------------------

 

[Tim Zych]

Combobox1.List = Range("A1:A100").Value

Hi All,

Please let me know some method for filling range of data to combobox on a userform from spreadsheet.
The range data will be present on the spreadsheet.
I filled the combo by entering individual cell values in the VBA

code.Instead of doing like this

is there any method of selecting a range of values from a sheet and put

the same data into the combo in one go.