Prev: Form question
Next: Calling VB pgm from DOS, thence exiting to DOS after a CHDIR
From: Jack B. Pollack on 12 Apr 2010 18:30
I am trying to format text in a textbox so it is in 3 columns (I'm using a
fixed width font so it will align correctly).
The 3rd column is a number. I would like it to be decimal point aligned
(with possibly 1 leading zero - not 2 or 3 leading zeros).

So I would like the output to be:
AAAAA BBBBBBB 123.45
AAA BBBBB 0.11
AAAAA BBBBBBB 3.20

The code below aligns correctly but gives me 2 extra leading zeros. I
basically want this output but have the 2 leading digits replaced with a
space if no digit is present.

Text1.SelText = " " & Format$((DataItem(1), "!@@@@@") & " " &
Format$((DataItem(2), "!@@@@@@@") & " " & Format$(DataItem(3), "000.00") &
Chr$(13) & Chr$(10)



I also tried Format$(DataItem(3), "##0.00")


From: Phil Hunt on 12 Apr 2010 19:00
I think you need to loop thu all the numbers and find the biggest one before
you can assign a format. When you do, you can create a format mask
dynamically using

Format(nNumber, String$(vLen, "@"))
You need to tweak the 2nd parameter to suite your need.


"Jack B. Pollack" <N(a)NE.nothing> wrote in message
news:%23qzhZ$o2KHA.5880(a)TK2MSFTNGP02.phx.gbl...
>I am trying to format text in a textbox so it is in 3 columns (I'm using a
>fixed width font so it will align correctly).
> The 3rd column is a number. I would like it to be decimal point aligned
> (with possibly 1 leading zero - not 2 or 3 leading zeros).
>
> So I would like the output to be:
> AAAAA BBBBBBB 123.45
> AAA BBBBB 0.11
> AAAAA BBBBBBB 3.20
>
> The code below aligns correctly but gives me 2 extra leading zeros. I
> basically want this output but have the 2 leading digits replaced with a
> space if no digit is present.
>
> Text1.SelText = " " & Format$((DataItem(1), "!@@@@@") & " " &
> Format$((DataItem(2), "!@@@@@@@") & " " & Format$(DataItem(3), "000.00")
> & Chr$(13) & Chr$(10)
>
>
>
> I also tried Format$(DataItem(3), "##0.00")
>


From: Bob Butler on 12 Apr 2010 19:27

"Jack B. Pollack" <N(a)NE.nothing> wrote in message
news:%23qzhZ$o2KHA.5880(a)TK2MSFTNGP02.phx.gbl...
>I am trying to format text in a textbox so it is in 3 columns (I'm using a
>fixed width font so it will align correctly).
> The 3rd column is a number. I would like it to be decimal point aligned
> (with possibly 1 leading zero - not 2 or 3 leading zeros).
>
> So I would like the output to be:
> AAAAA BBBBBBB 123.45
> AAA BBBBB 0.11
> AAAAA BBBBBBB 3.20
>
> The code below aligns correctly but gives me 2 extra leading zeros. I
> basically want this output but have the 2 leading digits replaced with a
> space if no digit is present.
>
> Text1.SelText = " " & Format$((DataItem(1), "!@@@@@") & " " &
> Format$((DataItem(2), "!@@@@@@@") & " " & Format$(DataItem(3), "000.00")
> & Chr$(13) & Chr$(10)

Try something like
right$(" " & format$(DataItem(3), "##0.00"),6)

BTW, you might find it more readable to use "& vbcrlf" instead of "&
chr$(13) & chr$(10)"

From: Jack B. Pollack on 12 Apr 2010 19:48
Thanks guys

"Jack B. Pollack" <N(a)NE.nothing> wrote in message
news:%23qzhZ$o2KHA.5880(a)TK2MSFTNGP02.phx.gbl...
>I am trying to format text in a textbox so it is in 3 columns (I'm using a
>fixed width font so it will align correctly).
> The 3rd column is a number. I would like it to be decimal point aligned
> (with possibly 1 leading zero - not 2 or 3 leading zeros).
>
> So I would like the output to be:
> AAAAA BBBBBBB 123.45
> AAA BBBBB 0.11
> AAAAA BBBBBBB 3.20
>
> The code below aligns correctly but gives me 2 extra leading zeros. I
> basically want this output but have the 2 leading digits replaced with a
> space if no digit is present.
>
> Text1.SelText = " " & Format$((DataItem(1), "!@@@@@") & " " &
> Format$((DataItem(2), "!@@@@@@@") & " " & Format$(DataItem(3), "000.00")
> & Chr$(13) & Chr$(10)
>
>
>
> I also tried Format$(DataItem(3), "##0.00")
>


From: GS on 12 Apr 2010 20:12
I'm not convinced that you want to use Format() with the string of
numbers. Since you are putting this data into a textbox then it has to
be a string already and so all that's needed is to pad with leading
spaces so your decimal aligns. I think Phil has the right idea about
first looping to determine the Len() of the longest data item. Place
this value in his vLen variable and process the array element something
like this:

Sub AlignTextboxColumns()
Dim sText As String, sNum As String, vNums As Variant
Dim vLen As Variant

vLen = 10 'replace with actual of longest string
vNums = Array("123.45", "0.22", "1,012.44")

sNum = Space$(vLen - Len(vNums(0))) & vNums(0)
sText = Space$(1) & Format$("AAAAAA", "!@@@@@") & Space$(4) & _
Format$("BBBBBBBBB", "!@@@@@@@") & Space(2) & sNum & vbCrLf

sNum = Space$(vLen - Len(vNums(1))) & vNums(1)
sText = sText & Space$(1) & Format$("AAA", "!@@@@@") & Space$(4) & _
Format$("BBBBBBB", "!@@@@@@@") & Space(2) & sNum & vbCrLf

sNum = Space$(vLen - Len(vNums(2))) & vNums(2)
sText = sText & Space$(1) & Format$("AA", "!@@@@@") & Space$(4) & _
Format$("BBBB", "!@@@@@@@") & Space(2) & sNum & vbCrLf

Debug.Print sText
End Sub

Immediate Window output:

AAAAA BBBBBBB 123.45
AAA BBBBBBB 0.22
AA BBBB 1,012.44

HTH
--
Garry


 |  Next  |  Last
Pages: 1 2
Prev: Form question
Next: Calling VB pgm from DOS, thence exiting to DOS after a CHDIR