ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   Listbox with Multiple Columns (https://www.excelbanter.com/excel-programming/432552-listbox-multiple-columns.html)

Eddie_SP[_2_]
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...

Dave Peterson
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

Eddie_SP[_2_]
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

Dave Peterson
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

Eddie_SP[_2_]
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


All times are GMT +1. The time now is 08:19 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com