PDA

View Full Version : [SOLVED] Data transfer to ListBox



TheGreatest
12-15-2004, 03:16 AM
Hi,

I have a Workbook which contains 40 worksheets. I created a UserForm and I have a ComboBox and a ListBox on the UserForm.
ComboBox will show the sheet names to pick and ListBox will show the data in Range(C35:K61) on the sheet which picked by the combobox.
I menaged ComboBox to work in order but couldn't managed the ListBox to show the data.

Any help would be appreciated...

Regards

Jacob Hilderbrand
12-15-2004, 03:26 AM
Try this code. I also attached an example.



Option Explicit

Private Sub ComboBox1_Change()
Dim InputRange As Range
Dim Cel As Range
Me.ListBox1.Clear
Set InputRange = Sheets(Me.ComboBox1.Text).Range("C35:K61")
For Each Cel In InputRange
If Cel.Text <> "" Then
Me.ListBox1.AddItem (Cel.Text)
End If
Next
End Sub

Private Sub UserForm_Initialize()
Dim WS As Worksheet
For Each WS In Worksheets
Me.ComboBox1.AddItem (WS.Name)
Next
End Sub

TheGreatest
12-15-2004, 04:29 AM
Hi DRJ,


Thanks for reply, the code you gave worked but filled only one line in the ListBox from top to bottom. The way I wanted was bring all the columns in range (C35:K61) from left to right. Thanks for the help.

I found this code somewhere else and it worked as the way I wanted

Private Sub ComboBox1_Change()
ListBox1.RowSource = ComboBox1.Value & "!C35:K61"
End Sub

Regards

Jacob Hilderbrand
12-15-2004, 04:40 AM
Glad you got it working.

Take Care