How to align decimals in a Listbox

[Guest]

I would like to have a column in a list box that contains numbers with
decimals and have the decimals aligned. Another column has text that
should be left aligned. Is there ANY way to do this.

A similar question: Can I apply the format of the cells in the worksheet to
the "same" "cells" in the list box? (If the contents of cell A6 is
displayed in a listbox, I would like the format of cell A6 to be used.)

Many thanks.

 

[Dave Peterson]

Can you use a non-proportional font--like Courier New?

If yes...

I created a small userform with a listbox and a couple of command buttons in
it. And used a two column range and formatted the second column by specifying a
numberformat and adding some leading spaces.

Option Explicit
Private Sub CommandButton1_Click()
Dim iCtr As Long
With Me.ListBox1
For iCtr = 0 To .ListCount - 1
If .Selected(iCtr) Then
MsgBox .List(iCtr, 0) & "--" & CDbl(.List(iCtr, 1))
End If
Next iCtr
End With
End Sub
Private Sub CommandButton2_Click()
Unload Me
End Sub
Private Sub UserForm_Initialize()
Dim myRng As Range
Dim myCell As Range

With Worksheets("Sheet1")
Set myRng = .Range("a1:b10")
End With

With Me.ListBox1
.MultiSelect = fmMultiSelectMulti
.ColumnCount = myRng.Columns.Count

.Font = "Courier New"
For Each myCell In myRng.Columns(1).Cells
.AddItem myCell.Value
.List(.ListCount - 1, 1) = NicelySpaced(myCell.Offset(0, 1).Value)
Next myCell
End With

With Me.CommandButton1
.Caption = "Ok"
.Default = True
End With

With Me.CommandButton2
.Caption = "Cancel"
.Cancel = True
End With

End Sub
Function NicelySpaced(myVal As Double) As String
Dim myStr As String
Dim HowWide As Long

HowWide = 15
myStr = Format(myVal, "#,###.####")
myStr = Right(Space(HowWide) & myStr, HowWide)
NicelySpaced = myStr
End Function

 

[Martin Fishlock]

1. You need to format the numbers into a string and then put them onto to the
list box. ie:

s = Format(1.22223, "#,##0.00")
You may then need to pad the left had side and there are always problems
with bigger numbers.

For the text you need to pad it with spaces. (ie check the length with len()
and then add spaces in the right hand side.


2. As seen in the format function you can use the same formats as the cells.