Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Listbox with Multiple Columns
Hi !
I have one Listbox in my Form, and if the value of "Column A" is the same of the Combobox of the Form, it must be shown on List box the Column "B" and "C" values... But the value of Column "A" can be repeated below in other rows... I have the following, but it doesn't work: Dim ComboRef As String Dim i As Integer Dim RNG As Range ComboRef = Me.ComboBox1.Value i = 0 Worksheets(6).Activate While (ActiveSheet.Cells(1 + i, 2) < 0) i = i + 1 If Cells(1 + i, 1) = ComboRef Then RNG = Range(Cells(1 + i, 2), Cells(1 + i, 3)) With Me.ListBox1 .BoundColumn = 1 .ColumnCount = 2 .ColumnHeads = True .TextColumn = True .ListIndex = i .AddItem RNG End With End If Wend Please someone help me... |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Listbox with Multiple Columns
This worked ok for me:
Option Explicit Private Sub ComboBox1_Change() Dim wks As Worksheet Dim myRng As Range Dim myCell As Range If Me.ComboBox1.ListIndex < 0 Then 'nothing selected Beep Exit Sub End If 'I wouldn't rely on the postion of the worksheet (6). 'I'd use its name (or its codename) Set wks = Worksheets("Sheet1") With wks 'headers in row 1 of that worksheet??? Set myRng = .Range("a2", .Cells(.Rows.Count, "A").End(xlUp)) End With For Each myCell In myRng.Cells If LCase(myCell.Value) = LCase(Me.ComboBox1.Value) Then With Me.ListBox1 .AddItem myCell.Offset(0, 1).Value 'column B .List(.ListCount - 1, 1) = myCell.Offset(0, 2).Value 'column C End With End If Next myCell End Sub Private Sub CommandButton1_Click() Unload Me End Sub Private Sub UserForm_Initialize() 'some test data With Me.ComboBox1 .AddItem "A1" .AddItem "A2" .AddItem "A3" .AddItem "A4" End With With Me.ListBox1 .ColumnCount = 2 .ColumnWidths = "100;100" .MultiSelect = fmMultiSelectSingle End With End Sub Eddie_SP wrote: Hi ! I have one Listbox in my Form, and if the value of "Column A" is the same of the Combobox of the Form, it must be shown on List box the Column "B" and "C" values... But the value of Column "A" can be repeated below in other rows... I have the following, but it doesn't work: Dim ComboRef As String Dim i As Integer Dim RNG As Range ComboRef = Me.ComboBox1.Value i = 0 Worksheets(6).Activate While (ActiveSheet.Cells(1 + i, 2) < 0) i = i + 1 If Cells(1 + i, 1) = ComboRef Then RNG = Range(Cells(1 + i, 2), Cells(1 + i, 3)) With Me.ListBox1 .BoundColumn = 1 .ColumnCount = 2 .ColumnHeads = True .TextColumn = True .ListIndex = i .AddItem RNG End With End If Wend Please someone help me... -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Listbox with Multiple Columns
Hi Dave !!! Worked 90% !!! =)
But if I choose another value on Combobox1, in the Listbox1, the old values, they stay there, do you know how do I "clear" those values? I tried before "For Each MyCell" the command like: Me.Listbox1 = Clear But it didn't work ! Dave, thank you man, as always !!! Eddie. "Dave Peterson" wrote: This worked ok for me: Option Explicit Private Sub ComboBox1_Change() Dim wks As Worksheet Dim myRng As Range Dim myCell As Range If Me.ComboBox1.ListIndex < 0 Then 'nothing selected Beep Exit Sub End If 'I wouldn't rely on the postion of the worksheet (6). 'I'd use its name (or its codename) Set wks = Worksheets("Sheet1") With wks 'headers in row 1 of that worksheet??? Set myRng = .Range("a2", .Cells(.Rows.Count, "A").End(xlUp)) End With For Each myCell In myRng.Cells If LCase(myCell.Value) = LCase(Me.ComboBox1.Value) Then With Me.ListBox1 .AddItem myCell.Offset(0, 1).Value 'column B .List(.ListCount - 1, 1) = myCell.Offset(0, 2).Value 'column C End With End If Next myCell End Sub Private Sub CommandButton1_Click() Unload Me End Sub Private Sub UserForm_Initialize() 'some test data With Me.ComboBox1 .AddItem "A1" .AddItem "A2" .AddItem "A3" .AddItem "A4" End With With Me.ListBox1 .ColumnCount = 2 .ColumnWidths = "100;100" .MultiSelect = fmMultiSelectSingle End With End Sub Eddie_SP wrote: Hi ! I have one Listbox in my Form, and if the value of "Column A" is the same of the Combobox of the Form, it must be shown on List box the Column "B" and "C" values... But the value of Column "A" can be repeated below in other rows... I have the following, but it doesn't work: Dim ComboRef As String Dim i As Integer Dim RNG As Range ComboRef = Me.ComboBox1.Value i = 0 Worksheets(6).Activate While (ActiveSheet.Cells(1 + i, 2) < 0) i = i + 1 If Cells(1 + i, 1) = ComboRef Then RNG = Range(Cells(1 + i, 2), Cells(1 + i, 3)) With Me.ListBox1 .BoundColumn = 1 .ColumnCount = 2 .ColumnHeads = True .TextColumn = True .ListIndex = i .AddItem RNG End With End If Wend Please someone help me... -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Listbox with Multiple Columns
Try...
End With Me.ListBox1.Clear '<-- added For Each myCell In myRng.Cells Eddie_SP wrote: Hi Dave !!! Worked 90% !!! =) But if I choose another value on Combobox1, in the Listbox1, the old values, they stay there, do you know how do I "clear" those values? I tried before "For Each MyCell" the command like: Me.Listbox1 = Clear But it didn't work ! Dave, thank you man, as always !!! Eddie. "Dave Peterson" wrote: This worked ok for me: Option Explicit Private Sub ComboBox1_Change() Dim wks As Worksheet Dim myRng As Range Dim myCell As Range If Me.ComboBox1.ListIndex < 0 Then 'nothing selected Beep Exit Sub End If 'I wouldn't rely on the postion of the worksheet (6). 'I'd use its name (or its codename) Set wks = Worksheets("Sheet1") With wks 'headers in row 1 of that worksheet??? Set myRng = .Range("a2", .Cells(.Rows.Count, "A").End(xlUp)) End With For Each myCell In myRng.Cells If LCase(myCell.Value) = LCase(Me.ComboBox1.Value) Then With Me.ListBox1 .AddItem myCell.Offset(0, 1).Value 'column B .List(.ListCount - 1, 1) = myCell.Offset(0, 2).Value 'column C End With End If Next myCell End Sub Private Sub CommandButton1_Click() Unload Me End Sub Private Sub UserForm_Initialize() 'some test data With Me.ComboBox1 .AddItem "A1" .AddItem "A2" .AddItem "A3" .AddItem "A4" End With With Me.ListBox1 .ColumnCount = 2 .ColumnWidths = "100;100" .MultiSelect = fmMultiSelectSingle End With End Sub Eddie_SP wrote: Hi ! I have one Listbox in my Form, and if the value of "Column A" is the same of the Combobox of the Form, it must be shown on List box the Column "B" and "C" values... But the value of Column "A" can be repeated below in other rows... I have the following, but it doesn't work: Dim ComboRef As String Dim i As Integer Dim RNG As Range ComboRef = Me.ComboBox1.Value i = 0 Worksheets(6).Activate While (ActiveSheet.Cells(1 + i, 2) < 0) i = i + 1 If Cells(1 + i, 1) = ComboRef Then RNG = Range(Cells(1 + i, 2), Cells(1 + i, 3)) With Me.ListBox1 .BoundColumn = 1 .ColumnCount = 2 .ColumnHeads = True .TextColumn = True .ListIndex = i .AddItem RNG End With End If Wend Please someone help me... -- Dave Peterson -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Listbox with Multiple Columns
It did woooork !!! =)
\o/ Thank you Dave !!! Very very much ! =) "Dave Peterson" wrote: Try... End With Me.ListBox1.Clear '<-- added For Each myCell In myRng.Cells Eddie_SP wrote: Hi Dave !!! Worked 90% !!! =) But if I choose another value on Combobox1, in the Listbox1, the old values, they stay there, do you know how do I "clear" those values? I tried before "For Each MyCell" the command like: Me.Listbox1 = Clear But it didn't work ! Dave, thank you man, as always !!! Eddie. "Dave Peterson" wrote: This worked ok for me: Option Explicit Private Sub ComboBox1_Change() Dim wks As Worksheet Dim myRng As Range Dim myCell As Range If Me.ComboBox1.ListIndex < 0 Then 'nothing selected Beep Exit Sub End If 'I wouldn't rely on the postion of the worksheet (6). 'I'd use its name (or its codename) Set wks = Worksheets("Sheet1") With wks 'headers in row 1 of that worksheet??? Set myRng = .Range("a2", .Cells(.Rows.Count, "A").End(xlUp)) End With For Each myCell In myRng.Cells If LCase(myCell.Value) = LCase(Me.ComboBox1.Value) Then With Me.ListBox1 .AddItem myCell.Offset(0, 1).Value 'column B .List(.ListCount - 1, 1) = myCell.Offset(0, 2).Value 'column C End With End If Next myCell End Sub Private Sub CommandButton1_Click() Unload Me End Sub Private Sub UserForm_Initialize() 'some test data With Me.ComboBox1 .AddItem "A1" .AddItem "A2" .AddItem "A3" .AddItem "A4" End With With Me.ListBox1 .ColumnCount = 2 .ColumnWidths = "100;100" .MultiSelect = fmMultiSelectSingle End With End Sub Eddie_SP wrote: Hi ! I have one Listbox in my Form, and if the value of "Column A" is the same of the Combobox of the Form, it must be shown on List box the Column "B" and "C" values... But the value of Column "A" can be repeated below in other rows... I have the following, but it doesn't work: Dim ComboRef As String Dim i As Integer Dim RNG As Range ComboRef = Me.ComboBox1.Value i = 0 Worksheets(6).Activate While (ActiveSheet.Cells(1 + i, 2) < 0) i = i + 1 If Cells(1 + i, 1) = ComboRef Then RNG = Range(Cells(1 + i, 2), Cells(1 + i, 3)) With Me.ListBox1 .BoundColumn = 1 .ColumnCount = 2 .ColumnHeads = True .TextColumn = True .ListIndex = i .AddItem RNG End With End If Wend Please someone help me... -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Additems from one listbox with multiple columns to another | Excel Programming | |||
deleting corresponding columns on a multiple select listbox | Excel Programming | |||
adding multiple columns to a listbox | Excel Programming | |||
multiple columns / rows to be referenced through a listbox | Excel Discussion (Misc queries) | |||
Selecting multiple items and columns from ListBox | Excel Programming |