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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dale FyeOwner, Developing Solutions LLCCommented:
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
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
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
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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 ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.