Get range in list from combobox value

[Axel]

I have a userform, with a combobox and two listboxes. I use this code to
get the sheets names from the workbook, to the combobox:
Private Sub UserForm_Initialize()
Dim ws As Integer
For ws = 1 To Sheets.Count
ComboBox1.AddItem Sheets(ws).Name
Next
End Sub
but I want to get range "B2:B50" in listbox1 from the sheet name, who is
selected with combobox1.
I have no ideas how to solve this, and the sheet names can be changed
from one day to another, so a case statement is no good here.
Anyone please!

 

[Guest]

Sub test()

For Each ws In Worksheets
ComboBox1.AddItem ws.Name
Next ws
Mysheet = ComboBox1.Text
With Sheets(Mysheet)
For Each cell In .Range("B2:B50")

ListBox1.AddItem cell
Next cell
End With
End Sub

 

[Axel]

Thanks for answer!
I am still struggle with this.
am not sure that I dim the code correct.
Private Sub ComboBox1_Change()
Dim ws As Integer
Dim cell As range
For ws = 1 To Sheets.Count
ComboBox1.AddItem Sheets(ws).Name
Next
'ws returns sheet count? Combo returns sheet name?
ws = ComboBox1.Text
With Sheets(ws)
For Each cell In .range("B2:B50")

lbAvailableItems.AddItem cell
Next cell
End With
End Sub

Any suggestion?

 

[Leith Ross]

I have a userform, with a combobox and two listboxes. I use this code to
get the sheets names from the workbook, to the combobox:
Private Sub UserForm_Initialize()
Dim ws As Integer
For ws = 1 To Sheets.Count
ComboBox1.AddItem Sheets(ws).Name
Next
End Sub
but I want to get range "B2:B50" in listbox1 from the sheet name, who is
selected with combobox1.
I have no ideas how to solve this, and the sheet names can be changed
from one day to another, so a case statement is no good here.
Anyone please!

*** Sent via Developersdexhttp://www.developersdex.com***

Hello Axel,

To load ListBox1 when a selection is made in ComboBox1, you need to
have a macro in the Click event for the ComboBox.

Sub ComboBox1_Click()

Dim Cell As Range
Dim Wks As Worksheet

With ComboBox1
If .ListIndex = -1 Then Exit Sub
Wks = .List(.ListIndex)
End With

With ListBox1
.Clear
For Each Cell In Wks.Range("B2:B50")
.AddItem Cell.Value
Next Cell
End With

End Sub

Sincerely,
Leith Ross

 

[Axel]

Thank you very much!
Axel