Data transfer to ListBox

[TheGreatest]

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]

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]

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]

Glad you got it working.

Take Care