Setting Tab Stops in a ListBox

B

Bdra

New Member
Joined
Jul 19, 2008
Messages
41
I am trying to organize data in two columns in a listbox on a userform. Something like the following:

Code: 
List1 = Array("Height", "Weight", "Depth")
List2 = Array(10, 20, 30)

For i = 0 to UBound(List1)
      MyForm.ListBox1.AddItem List1(i) & vbTab & List2(i)
Next
This works as far as it goes, but the vbTab just grabs whatever tab stop is available next, and as a result the List2 items fail to align in a neat column.

In all my searching online I have found only one basic solution, which is based on VB6, not VBA. The solution is simply to clear the existing tab stops in the listbox and set a new one. The problem is that this doesn't seem to work in VBA.

Code: 
Private Declare Function SendMessage Lib "user32" Alias "SendMessageA" (ByVal hwnd As Long, ByVal wMsg As Long, ByVal wParam As Long, lParam As Any) As Long
Const LB_SETTABSTOPS = &H192

TabLength = 80 'Tab length in pixels

SendMessage MyForm.ListBox1.hwnd, LB_SETTABSTOPS, 0&, 0&
SendMessage MyForm.ListBox1.hwnd, LB_SETTABSTOPS, 1&, TabLength
This produces a "Compile Error: Method or data member not found," where ".hwnd" on the first SendMessage line is highlighted.

I'm a little out of my depth here, and so I am stuck. Usually this type of API call works fine in VBA -- so is the problem that VBA does not recognize that a listbox has a window handle? That some reference needs to be set? Or something else entirely?

And if this just can't be made to work within VBA, is there another way to get to the goal -- a neat, two-column display in the listbox?
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
Sort by date Sort by votes
Mike,

Duh! Thank you. Much simpler and works as intended. (You meant the ColumnCount property.)

For the benefit of anyone else who may be reading this in the future: A two-column listbox can be populated with a two-dimensional array, something like this:

Code: 
List1(0, 0) = "Height"
List1(0, 1) = 10
List1(1, 0) = "Weight"
List1(1, 1) = 20
List1(2, 0) = "Depth"
List1(2, 1) = 30

MyForm.ListBox1.List = List1
It took a little gymnastics to get this to work correctly with a dynamic array, because it is only possible to ReDim Preserve the last index. IOW, you can dynamically increase the number of columns, but not the number of rows. I wanted the reverse -- to create as many rows as I had data for, but stay with two columns.

In order to populate the listbox correctly, I first had to create two separate one-dimensional arrays, then ReDim the two-dimensional array and populate it with the data from the two separate arrays:

Code: 
ReDim DisplayList(UBound(List1), 1)
                
For j = 0 To UBound(List1)
     DisplayList(j, 0) = List1(j)
     DisplayList(j, 1) = List2(j)
Next
                
MyForm.ListBox1.List = DisplayList
As for my original question, I found some info here: http://www.vbforums.com/showthread.php?t=544971. It looks like VBA does not recognize the window handle of a listbox; it needs to be created as a new class. (But it would take a more demanding assignment than mine to justify the trouble.)
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Question
What is wrong with this VBA code: userForm get hand ico for pointer on hover
2
Replies
15
Views
525
RoryA
RoryA
H
  • Solved
Catching the Mouse Click Error in Modal User Form
Replies
4
Views
304
huiettcm
H
A
  • Question
Ask VBA how to Print separate PDF
Replies
6
Views
658
ceranes
C
M
  • Question
Code works however receiving Runtime error 380 - Could not set the list property. Invalid property value
Replies
2
Views
506
Megg
M
M
  • Question
object required error when loop within subfolders in selected row from listbox on userform
Replies
0
Views
296
Mussala
M

Forum statistics

Threads
1,215,008
Messages
6,122,672
Members
449,091
Latest member
peppernaut

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top