Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Get range in list from combobox value
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 Developersdex http://www.developersdex.com *** |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Get range in list from combobox value
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" wrote: 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 Developersdex http://www.developersdex.com *** |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Get range in list from combobox value
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? *** Sent via Developersdex http://www.developersdex.com *** |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Get range in list from combobox value
On Jul 19, 8:51 am, Axel wrote:
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Get range in list from combobox value
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Combobox List to list Numerical Values ONLY in Column A is specific Sheet.... | Excel Programming | |||
ComboBox list reliant on the entry from a different ComboBox | Excel Programming | |||
Dynamically assign ComboBox.List from named range areas | Excel Programming | |||
ComboBox List Fill Range | Excel Programming | |||
missing items in sheet's activex list/combobox after resizing range | Excel Programming |