I'm in a situation where I need to use the VBA Listbox control to show 1 piece of data per row, but store 3 pieces of data. After a futile attempt at trying to attach a custom object to each list item, I'm now attempting to use 3 columns, but hiding column 2-3, by making column 1 as wide as the listbox and hiding the horizontal scrollbar that appears.

I found an API function for this, but VBA complains about the nonexisting .hwnd property.

Private Declare Function ShowScrollBar Lib "user32" (ByVal hwnd As Long, ByVal wBar As Long, ByVal bShow As Long) As Long

Private Const SB_HORZ = 0 ' Horizontal Scrollbar
Private Const SB_VERT = 1 ' Vertical Scrollbbar
Private Const SB_BOTH = 3 ' Both ScrollBars

....

ShowScrollBar Listbox1.hwnd, SB_HORZ, False

Any ideas?

Gustaf

Nevermind, I found a cleaner solution. Column 2-3 can also be hidden by setting their width to 0 in ColumnWidths (the property holds a semicolon-separated list of pixel widths). Hope it helps someone. :-)

Gustaf

--

Gustaf wrote:
I'm in a situation where I need to use the VBA Listbox control to show 1
piece of data per row, but store 3 pieces of data. After a futile
attempt at trying to attach a custom object to each list item, I'm now
attempting to use 3 columns, but hiding column 2-3, by making column 1
as wide as the listbox and hiding the horizontal scrollbar that appears.

I found an API function for this, but VBA complains about the
nonexisting .hwnd property.

Private Declare Function ShowScrollBar Lib "user32" (ByVal hwnd As Long,
ByVal wBar As Long, ByVal bShow As Long) As Long

Private Const SB_HORZ = 0 ' Horizontal Scrollbar
Private Const SB_VERT = 1 ' Vertical Scrollbbar
Private Const SB_BOTH = 3 ' Both ScrollBars

...

ShowScrollBar Listbox1.hwnd, SB_HORZ, False

Any ideas?

Gustaf

re listbox
set column count to 3
set column widths to ;0;0
which will hide columns 2 & 3

now either set the rowsource to 3 columns eg A5:C15

or code the population

Dim cell As Range
Set cell = Range("A1")
Do Until cell = ""
With ListBox1
.AddItem cell.Value ' column 1 (0 base)
.List(.ListCount - 1, 1) = cell.Offset(, 1).Value ' column 2
.List(.ListCount - 1, 2) = cell.Offset(, 2).Value ' column 3
End With
Set cell = cell.Offset(1)
Loop





"Gustaf" wrote:

I'm in a situation where I need to use the VBA Listbox control to show 1 piece of data per row, but store 3 pieces of data. After a futile attempt at trying to attach a custom object to each list item, I'm now attempting to use 3 columns, but hiding column 2-3, by making column 1 as wide as the listbox and hiding the horizontal scrollbar that appears.

I found an API function for this, but VBA complains about the nonexisting .hwnd property.

Private Declare Function ShowScrollBar Lib "user32" (ByVal hwnd As Long, ByVal wBar As Long, ByVal bShow As Long) As Long

Private Const SB_HORZ = 0 ' Horizontal Scrollbar
Private Const SB_VERT = 1 ' Vertical Scrollbbar
Private Const SB_BOTH = 3 ' Both ScrollBars

....

ShowScrollBar Listbox1.hwnd, SB_HORZ, False

Any ideas?

Gustaf