ListBox Show Multiple columns

[gheyman]

On my userform I have a List box and in the properties I see it asks for Bound Column, Column Count... So I assume you can show more than one column in the list.

RowSource: Drop_Down_Lists!$J$3:$K$100

I set the column count to 2 the bound column I set to 2 and I made the widths so that it should show two (1 pt;0.5 pt)

But when I open the form, I only see the data from the 2nd column in the list.

And when I make selections, its putting the data from column 1 in my selected area.

What am I doing wrong?

 

[My Aswer Is This]

Re: ListBox Show Mutilpe columns

I never use RowSource
I use something like this:
Row Source is not dynamic

Remove the RowSouce from the Userform Project window
And the other things like column count and width.

This script will load in all these details when you open the Userform
And then if you add more data to your range of values in column K and J this will be automatically updated when you open your userform.

Put this script in your Userform

Private Sub UserForm_Initialize()
'Modified  10/30/2018  9:07:29 PM  EDT
Dim Data As Variant
Dim lastrow As Long
lastrow = Sheet1.Cells(Rows.Count, "K").End(xlUp).Row
ListBox1.ColumnCount = 2
ListBox1.ColumnWidths = "20;20"
Data = Range("J2:K" & lastrow)
ListBox1.List = Data
End Sub

 

[gheyman]

Re: ListBox Show Mutilpe columns

Thanks, but now I get an error when I try to activate the form. I am trying to open the form by either right click or double click. When I insert this code, I get an error when I double click.

Its a Run-time error '70':
Permission denied

Here is the code that errors

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
UserForm1.Show
End Sub

I appreciate the help! Thank you

 

[gheyman]

Re: ListBox Show Mutilpe columns

I fixed the problem with the error by changing lastrow = Sheet1 to Sheet6

But I still only see one row in my list and its storing the selections from column 1 when I need it to store the info from column 2

Private Sub CommandButton1_Click()
Dim x As Long
Dim varSelected() As String
Dim i As Integer

With ListBox1
   ReDim varSelected(1 To .ListCount)
    For x = 0 To .ListCount - 2
        If .Selected(x) Then
            i = i + 1
            varSelected(i) = .List(x)
        End If
    Next x
End With

If i = 0 Then Exit Sub

With Sheets("DROP_DOWN_LISTS")
   .Range("U3:U500").ClearContents
   .Range("U3").Resize(i).Value = Application.Transpose(varSelected)
End With
UserForm1.Hide

End Sub

 

[My Aswer Is This]

Re: ListBox Show Mutilpe columns

Not sure. But the code I provided works for me. I mentioned not using RowSource but you made no comment about that.
If you use my script after you have removed RowSource both listbox columns should show data.

Assuming you have data in columns J and K of the active sheet
And you must remove the column width and column count you entered.

Not sure what all this code your showing here is suppose to do.

I assume you did not use my suggestions. At least if you did you did not comment about if you removed all your previous entries in the userform. You cannot use both my suggestion and yours.

And since you never mentioned sheet names I just had to guess.

To help we always need specific details.

And I'm not sure what you ultimate goal is here.

And you never mentioned wanting to do this with a sheet activation event using doubleclick

 

[mikerickson]

Re: ListBox Show Mutilpe columns

I made the widths so that it should show two (1 pt;0.5 pt)

A typical character is at least 6 points wide.

With that setting, the first column would be 1 pt wide and the second would be the remainder of the listbox width.
That is why you can't see the first column.
Set the column widths wider.

 

[My Aswer Is This]

Re: ListBox Show Mutilpe columns

I wondered about that myself. That's why I provided 20. I never set these perrimiters in the Properties window. I always set them on userform initialization.

A typical character is at least 6 points wide.

With that setting, the first column would be 1 pt wide and the second would be the remainder of the listbox width.
That is why you can't see the first column.
Set the column widths wider.

 

[Norie]

Re: ListBox Show Mutilpe columns

In this code List(x) will always refer to the first column.

varSelected(i) = .List(x)

To refer to another column you need to use the column argument of List.

varSelected(i) = .List(x,1) ' return column value from column 2

By the way, I don't think I've ever used the BoundColumn property with a listbox in Excel.

 

[My Aswer Is This]

Re: ListBox Show Mutilpe columns

Me neither. What does that even do?

In this code List(x) will always refer to the first column.

varSelected(i) = .List(x)

To refer to another column you need to use the column argument of List.

varSelected(i) = .List(x,1) ' return column value from column 2

By the way, I don't think I've ever used the BoundColumn property with a listbox in Excel.

 

[Norie]

Re: ListBox Show Mutilpe columns

It determines which column in a multicolumn listbox that the value is returned from when you use the .Value property.