How make numbers in a listbox appear flush right?

Is there a way to make numbers in a listbox appear flush right?
SteveL13Asked:
Who is Participating?
 
Gustav BrockCIOCommented:
If you use the font MS Sans Serif  for the listbox (goes for a combobox as well), you can use a recordsource like this with three fields:

  SELECT 
    SomeField,
    Format([AmountMin], Space(14-2*Len([AmountMin])-Abs([AmountMin]>1000)) & "#,##0") AS SMin, 
    Format([AmountMax], Space(14-2*Len([AmountMax])-Abs([AmountMax]>1000)) & "#,##0") AS SMax
  FROM 
    YourTable
  ORDER BY 
    SomeField;

Open in new window

This takes advantage of the fact that space and dot and comma for this font consume half a "space" while digits consume a full "space".
Not very fancy but it works.
2
 
Dale FyeCommented:
Only way I've found to do this is to:
1.  Use a non-proportional font (something like Courier)
2.  Pad the numeric value with leading spaces, something like:

Right(Space(10) & [SomeField], 10)

Keep in mind that if you do this, then you will need to strip the leading spaces out of the column before you use the value from that column.
0
 
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
I have found En Space to be a good width for using Dale's method with numbers, if I recall correctly. Even with proportional font, they line up fairly well since each number is normally the same width.
  right(string(10, chrW(8194) ) & format( myNumber, "#,##0")  ,10)

Open in new window

chrW(8194) = En Space

have an awesome day,
crystal
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Ryan ChongCommented:
probably to use the ActiveX Listbox control instead in which it got the TextAlign property that can be set to Right.

SnapShot.png
but by doing this, you need to manually load the data into this ActiveX ListBox, like:

ListBox1.AddItem "test 1"
    ListBox1.AddItem "test 2"

Open in new window


Untitled1.png
0
 
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
> "MS Sans Serif ... space and dot and comma for this font consume half a "space" while digits consume a full "space"

thanks, Gustav ~ nice to know

have an awesome day,
crystal
0
 
Ryan ChongCommented:
Gustav's suggestion works for numerical values

a side note... what if it's text? can we using similar method to set it aligned to Right?
0
 
Gustav BrockCIOCommented:
MS Sans Serif  is a bit outdated. You may get better results with the OpenType "Lucida Sans".

Further, to handle negative values you will need a second section of the format specification.
Also, you may introduce Int to prevent errors for decimal numbers:

Format([Amount],Space(14-2*Len(Int([Amount]))-Abs([Amount]>1000)) & "#,##0;" & Space(15-2*Len(Int([Amount]))-Abs([Amount]>1000)) & "-#,##0")

Open in new window

1
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.