Listbox ColumnCount = 3

[Corey]

A previous post got no replies, so i am still stuck on this:

I have 3 Columns in a Listbox.

I am needing to place the value into a Sheet for printing, but am not sure HOW to ONLY get the
Column 1 (Left) Value into the cell.
Currently i seem to get ALL 3 Column values.

I am using this code to input the values:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Private Sub CommandButton2_Click()
Application.ScreenUpdating = False
UserForm9.Hide
With ActiveWorkbook.Worksheets("JC")
..Select
Dim rngFound As Range
On Error Resume Next
With Worksheets("Data").Range("A:A")
Set rngFound = .Find(What:=UserForm9.ListBox4.Value, After:=.Cells(1), LookIn:=xlValues,
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, Matchbyte:=False)
Range("B2").Value = UserForm9.ListBox4.Value <== This has 3 Columns. But i want ONLY the Value in
Column1 to be placed in Cell B2
Range("D6").value = rngFound.Offset(0,4).value
....
.....
......
End Sub

Corey....

 

[Dave Peterson]

I'm confused about what you're doing with Data and JC, but this skinnied down
version of your code worked fine for me.

Option Explicit
Private Sub CommandButton1_Click()
Unload Me
End Sub
Private Sub CommandButton2_Click()
Dim rngFound As Range
With Worksheets("Data").Range("A:A")
Set rngFound = .Find(What:=Me.ListBox1.Value, _
After:=.Cells(1), _
LookIn:=xlValues, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False, _
Matchbyte:=False)
.Range("B2").Value = Me.ListBox1.Value
End With
End Sub
Private Sub UserForm_Initialize()
Dim iCtr As Long
With Me.ListBox1
.ColumnCount = 3
'.value will return the boundcolumn's value
.BoundColumn = 1
'add some test data
For iCtr = 1 To 5
.AddItem "A" & iCtr
.List(.ListCount - 1, 1) = "B" & iCtr
.List(.ListCount - 1, 2) = "C" & iCtr
Next iCtr
End With
End Sub

The .boundcolumn property tells VBA what to use for the .Value. So I don't
understand why the .find would work, but the assignment to B2 failed.

 

[Corey]

Dave,

The Sheet "Data" is where exactly that is stored, data values from other userform information.

What i am doing is using form9 as a History search of specific data in the data sheet.

Among other values of data, the Listbox4 has 3 Columns of the data in it:

Job Number (data: Column A)
Date of Job (data: Column AQ)
and
Description of Job (data: Column BH)

The listbox4 is populated by PREVIOUS CONDITIONS that had to be met in Listboxes 1 and listbox2.

I have code in the form_Initialise to load the Listbox1 already.
Listboxes 1 & 2 are selections that the user needs to select to narrow down the search, such as:

Listbox1.list = customer names
Listbox2.list = Specific Job locations

Then Listbox4(Did have a lixtbox3 but did not need it, that's why it goes from 2 - 4) lists the 3
columns of information mentioned earlier.
The user then can scroll through the listbox4 list of Job Numbers/dates/Descriptions to find the one
they are after, then Click View Job card button.
This macro(as this step is at) needs to find the Listbox4 Column1(Job Number) and all other .Offset
values from the 'Data" Sheet and places it in a Sheet called "JC"(Job Cards").
JC sheet is a formatted layout that has the data placed in it and has company logos and is used by
the guys to inform of particulars of the job, sush as address contact details etc.

Hope this clears up what the sheets are for.

I am not sure if i can use the code you posted due to the other codes i have.

Currently i cannot get any other data into the JC sheet cause the code looks for the entire 3
columns of value in a single cell in column A, where ONLY the column 1(left column of Listbox4) has
the value i need.


Corey....



I'm confused about what you're doing with Data and JC, but this skinnied down
version of your code worked fine for me.

Option Explicit
Private Sub CommandButton1_Click()
Unload Me
End Sub
Private Sub CommandButton2_Click()
Dim rngFound As Range
With Worksheets("Data").Range("A:A")
Set rngFound = .Find(What:=Me.ListBox1.Value, _
After:=.Cells(1), _
LookIn:=xlValues, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False, _
Matchbyte:=False)
.Range("B2").Value = Me.ListBox1.Value
End With
End Sub
Private Sub UserForm_Initialize()
Dim iCtr As Long
With Me.ListBox1
.ColumnCount = 3
'.value will return the boundcolumn's value
.BoundColumn = 1
'add some test data
For iCtr = 1 To 5
.AddItem "A" & iCtr
.List(.ListCount - 1, 1) = "B" & iCtr
.List(.ListCount - 1, 2) = "C" & iCtr
Next iCtr
End With
End Sub

The .boundcolumn property tells VBA what to use for the .Value. So I don't
understand why the .find would work, but the assignment to B2 failed.

 

[Dave Peterson]

When you're populating the listbox4 list, is it really 3 different columns--or
is it one column that you've combined 3 different strings into:

Kind of like separate columns (A, B, C) in a worksheet or is it more like a
single column with:

=a1&" " & b1 & " " & c1
so it looks like 3 columns?

If you concatenated 3 strings into one, maybe you can de-concatenate (or parse)
the string to pick out the first "column". Search for the first space,
maybe????

But you may want to try dropping the concatenation and use 3 real columns.

If the values are in a contiguous range of 3 columns (and some number of rows),
you can pick them up all at once.

Kind of like:

Private Sub UserForm_Initialize()
Dim iCtr As Long
With Me.ListBox1
.ColumnCount = 3
.List = Worksheets("data").Range("a1:C9").Value
'.value will return the boundcolumn's value
.BoundColumn = 1
End With
End Sub

If the data is not contiguous -- rows or columns, you can loop through them and
use .additem to add the first column. Then use .list() to add the extra
columns.

I used this before:

Private Sub UserForm_Initialize()
Dim iCtr As Long
With Me.ListBox1
.ColumnCount = 3
'.value will return the boundcolumn's value
.BoundColumn = 1
'add some test data
For iCtr = 1 To 5
.AddItem "A" & iCtr
.List(.ListCount - 1, 1) = "B" & iCtr
.List(.ListCount - 1, 2) = "C" & iCtr
Next iCtr
End With
End Sub

I looped from 1 to 5, but you could loop through non-contiguous columns--or even
loop through the other listboxes lists.

 

[Corey]

I use this:

Private Sub ListBox2_Change()
Application.ScreenUpdating = False
If ListBox4.ListCount > 0 Then ListBox4.Clear
Dim LastCell As Long
Dim myrow As Long
On Error Resume Next
LastCell = Worksheets("Data").Cells(Rows.Count, "BH").End(xlUp).Row
With ActiveWorkbook.Worksheets("Data")
..Select
For myrow = 1 To LastCell
If .Cells(myrow, 60) <> "" Then
If .Cells(myrow, 60).Offset(0, 0).Value = ListBox2.Value Then
ListBox4.AddItem .Cells(myrow, 60).Offset(, -59) & vbTab & vbTab & _
.Cells(myrow, 60).Offset(, -17) & vbTab & vbTab & .Cells(myrow, 60).Offset(, -40) '<=== HERE
End If
End If
Next
End With
Label5 = "Below is a History of tasks carried out at " & ListBox1.Value & " on Conveyor named " &
ListBox2.Value
Application.ScreenUpdating = True
End Sub




Corey....

When you're populating the listbox4 list, is it really 3 different columns--or
is it one column that you've combined 3 different strings into:

Kind of like separate columns (A, B, C) in a worksheet or is it more like a
single column with:

=a1&" " & b1 & " " & c1
so it looks like 3 columns?

If you concatenated 3 strings into one, maybe you can de-concatenate (or parse)
the string to pick out the first "column". Search for the first space,
maybe????

But you may want to try dropping the concatenation and use 3 real columns.

If the values are in a contiguous range of 3 columns (and some number of rows),
you can pick them up all at once.

Kind of like:

Private Sub UserForm_Initialize()
Dim iCtr As Long
With Me.ListBox1
.ColumnCount = 3
.List = Worksheets("data").Range("a1:C9").Value
'.value will return the boundcolumn's value
.BoundColumn = 1
End With
End Sub

If the data is not contiguous -- rows or columns, you can loop through them and
use .additem to add the first column. Then use .list() to add the extra
columns.

I used this before:

Private Sub UserForm_Initialize()
Dim iCtr As Long
With Me.ListBox1
.ColumnCount = 3
'.value will return the boundcolumn's value
.BoundColumn = 1
'add some test data
For iCtr = 1 To 5
.AddItem "A" & iCtr
.List(.ListCount - 1, 1) = "B" & iCtr
.List(.ListCount - 1, 2) = "C" & iCtr
Next iCtr
End With
End Sub

I looped from 1 to 5, but you could loop through non-contiguous columns--or even
loop through the other listboxes lists.

 

[Corey]

Quote ""But you may want to try dropping the concatenation and use 3 real columns.""

How do i do this then ?

Corey....

When you're populating the listbox4 list, is it really 3 different columns--or
is it one column that you've combined 3 different strings into:

Kind of like separate columns (A, B, C) in a worksheet or is it more like a
single column with:

=a1&" " & b1 & " " & c1
so it looks like 3 columns?

If you concatenated 3 strings into one, maybe you can de-concatenate (or parse)
the string to pick out the first "column". Search for the first space,
maybe????

But you may want to try dropping the concatenation and use 3 real columns.

If the values are in a contiguous range of 3 columns (and some number of rows),
you can pick them up all at once.

Kind of like:

Private Sub UserForm_Initialize()
Dim iCtr As Long
With Me.ListBox1
.ColumnCount = 3
.List = Worksheets("data").Range("a1:C9").Value
'.value will return the boundcolumn's value
.BoundColumn = 1
End With
End Sub

If the data is not contiguous -- rows or columns, you can loop through them and
use .additem to add the first column. Then use .list() to add the extra
columns.

I used this before:

Private Sub UserForm_Initialize()
Dim iCtr As Long
With Me.ListBox1
.ColumnCount = 3
'.value will return the boundcolumn's value
.BoundColumn = 1
'add some test data
For iCtr = 1 To 5
.AddItem "A" & iCtr
.List(.ListCount - 1, 1) = "B" & iCtr
.List(.ListCount - 1, 2) = "C" & iCtr
Next iCtr
End With
End Sub

I looped from 1 to 5, but you could loop through non-contiguous columns--or even
loop through the other listboxes lists.

 

[Dave Peterson]

Check those previous posts. You'll see how it's done in the _initialize event.

 

[Dave Peterson]

For myrow = 1 To LastCell
If .Cells(myrow, 60) <> "" Then
If .Cells(myrow, 60).Offset(0, 0).Value = ListBox2.Value Then
ListBox4.AddItem .Cells(myrow, 60).Offset(, -59)
listbox4.list(listbox4.listcount-1,2) _
= .Cells(myrow, 60).Offset(, -17)
listbox4.list(listbox4.listcount-1,3) _
= .Cells(myrow, 60).Offset(, -40)
End If
End If
Next myrow

Make sure you include a line like:
listbox4.columncount = 3
Maybe in the userform_initialize event????

 

[Corey]

At the moment i have reverted the Listbox back to a single column of data and added another 2
listboxes for the other data related.

So know i have:

Private Sub ListBox2_Change()
Application.ScreenUpdating = False
If ListBox3.ListCount > 0 Then ListBox3.Clear
If ListBox4.ListCount > 0 Then ListBox4.Clear
If ListBox5.ListCount > 0 Then ListBox5.Clear
Dim LastCell As Long
Dim myrow As Long
On Error Resume Next
LastCell = Worksheets("Data").Cells(Rows.Count, "BH").End(xlUp).Row
With ActiveWorkbook.Worksheets("Data")
..Select
For myrow = 1 To LastCell
If .Cells(myrow, 60) <> "" Then
If .Cells(myrow, 60).Offset(0, 0).Value = ListBox2.Value Then
' ListBox4.AddItem .Cells(myrow, 60).Offset(, -59) & vbTab & vbTab & _
.Cells(myrow, 60).Offset(, -17) & vbTab & vbTab & .Cells(myrow, 60).Offset(, -40)
ListBox3.AddItem .Cells(myrow, 60).Offset(, -59) ' <=== lb1
ListBox4.AddItem .Cells(myrow, 60).Offset(, -17) ' <=== lb2
ListBox5.AddItem .Cells(myrow, 60).Offset(, -40) ' <=== lb3
End If
End If
Next
End With
Application.ScreenUpdating = True
End Sub

Am going to post a new post to see IF i can change the other 2 listboxes by the selected listbox1
value to have the correct ROWS of data.

Corey...


For myrow = 1 To LastCell
If .Cells(myrow, 60) <> "" Then
If .Cells(myrow, 60).Offset(0, 0).Value = ListBox2.Value Then
ListBox4.AddItem .Cells(myrow, 60).Offset(, -59)
listbox4.list(listbox4.listcount-1,2) _
= .Cells(myrow, 60).Offset(, -17)
listbox4.list(listbox4.listcount-1,3) _
= .Cells(myrow, 60).Offset(, -40)
End If
End If
Next myrow

Make sure you include a line like:
listbox4.columncount = 3
Maybe in the userform_initialize event????

 

[Corey]

Actually done it with:

Private Sub ListBox3_Change()
ListBox4.ListIndex = ListBox3.ListIndex
ListBox5.ListIndex = ListBox3.ListIndex
End Sub

Thanx Dave.


At the moment i have reverted the Listbox back to a single column of data and added another 2
listboxes for the other data related.

So know i have:

Private Sub ListBox2_Change()
Application.ScreenUpdating = False
If ListBox3.ListCount > 0 Then ListBox3.Clear
If ListBox4.ListCount > 0 Then ListBox4.Clear
If ListBox5.ListCount > 0 Then ListBox5.Clear
Dim LastCell As Long
Dim myrow As Long
On Error Resume Next
LastCell = Worksheets("Data").Cells(Rows.Count, "BH").End(xlUp).Row
With ActiveWorkbook.Worksheets("Data")
..Select
For myrow = 1 To LastCell
If .Cells(myrow, 60) <> "" Then
If .Cells(myrow, 60).Offset(0, 0).Value = ListBox2.Value Then
' ListBox4.AddItem .Cells(myrow, 60).Offset(, -59) & vbTab & vbTab & _
.Cells(myrow, 60).Offset(, -17) & vbTab & vbTab & .Cells(myrow, 60).Offset(, -40)
ListBox3.AddItem .Cells(myrow, 60).Offset(, -59) ' <=== lb1
ListBox4.AddItem .Cells(myrow, 60).Offset(, -17) ' <=== lb2
ListBox5.AddItem .Cells(myrow, 60).Offset(, -40) ' <=== lb3
End If
End If
Next
End With
Application.ScreenUpdating = True
End Sub

Am going to post a new post to see IF i can change the other 2 listboxes by the selected listbox1
value to have the correct ROWS of data.

Corey...


For myrow = 1 To LastCell
If .Cells(myrow, 60) <> "" Then
If .Cells(myrow, 60).Offset(0, 0).Value = ListBox2.Value Then
ListBox4.AddItem .Cells(myrow, 60).Offset(, -59)
listbox4.list(listbox4.listcount-1,2) _
= .Cells(myrow, 60).Offset(, -17)
listbox4.list(listbox4.listcount-1,3) _
= .Cells(myrow, 60).Offset(, -40)
End If
End If
Next myrow

Make sure you include a line like:
listbox4.columncount = 3
Maybe in the userform_initialize event????