PDA

View Full Version : [SOLVED] Justify text in a msgbox



Edmond
08-16-2019, 08:12 AM
Hello everyone,

I am trying to simulate 'columns' in a msgbox.

I though about measuring the length (len) of all the lines in the '1st' column and then add x * " " followed by the '|' character.
But the issue is the space doesn't have the same 'pixel length' of a number for instance.

With this formula:

WorksheetFunction.Rept(" ", lMaxStg - lenStg(i)) 'add space between last character and the limit set at '|'

I get that:

24830

which is of course nothing likes a proper column...

Would someone know how I can achieve a proper column?

thanks a lot!

Leith Ross
08-16-2019, 08:56 AM
Hello Edmond,

The main issue with getting things to line up is the font that is used. The easiest way is to use a fixed-pitch or monospaced font like Courier. Unfortunately, that is not a simple matter with a MsgBox as it require extensive use of the Windows API. It would be easier to create a UserForm to display the information. You have much more control over a UserForm and it's controls.

Edmond
08-16-2019, 09:05 AM
Thanks a lot Leith for your answer! I forgot to mention that the text will be copied to word so I can easily 'font format'.
I have used 'lenB' which gets me closer to the expected result:
I have no idea why I have still not a perfect line up though...

24831

Leith Ross
08-16-2019, 09:16 AM
Hello Edmond,

I am not sure I follow what your doing. Can you break it down for me step by step?

KOKOSEK
08-16-2019, 09:21 AM
24832

maybe this way ...


Sub MessBox()
Dim MsgInfo As String
Dim i, j As Long
For i = 1 To 4
For j = 1 To 4
MsgInfo = MsgInfo & Cells(i, j) & vbTab & vbTab
Next j
MsgInfo = MsgInfo & vbNewLine
Next i
MsgBox (MsgInfo)
End Sub

Edmond
08-16-2019, 09:30 AM
Below an extract of the code I am using:




lMaxStg = WorksheetFunction.Max(lentotal)

For i = 1 To UBound(dMCSum)

lenStg(i) = " -" & arrMC(i, 1) & ": " _
& dMCCount(i) & ", " & FormatCurrency(dMCSum(i))


stg(3) = stg(3) & " -" & arrMC(i, 1) & ": " _
& dMCCount(i) & ", " & FormatCurrency(dMCSum(i)) _
& WorksheetFunction.Rept(ChrB(32), lMaxStg - lenStg(i) + 1) _
& "| "

Next


Thanks Kokosek. I am going to try it.
The only thing is the length of the 1st "block" of text is variable. Thus I may not know how many 'tabs' I should use.

Leith Ross
08-16-2019, 09:31 AM
Hello KOKOSEK,

Works well if the formats of each cell are the same. What Edmond has presented has very mixed formats.

Leith Ross
08-16-2019, 09:35 AM
Hello Edmond,

Okay, I understand what your doing. The font of a message box is preset. The font of the message is disregarded and the preset of the Message Box is used.

Edmond
08-16-2019, 09:47 AM
I use the msgbox only as an indicator. As I won't use it at the end.
I tried to used a monospaced font in word but the result is the same...

KOKOSEK
08-16-2019, 09:57 AM
Hmm, so maybe do the loop through length of text from 1st block, find longest.
And by loop do something like NewText = OrgText & REPT(CHAR(13),MAX(as above) - LEN(OrgText)) then all NewTexts should have the same length.
Like below:
Excel 2016 (Windows) 32 bit




A


B


C


D


E




1

VARLENGTHVARLENGTHVARLENGTH

27


VARLENGTHVARLENGTHVARLENGTH

45




2

VARLENGTHVARLENGTH

18


VARLENGTHVARLENGTH

45




3

VARLENGTHVARLENGTHVARLENGTHVARLENGTHVARLENGTH

45


VARLENGTHVARLENGTHVARLENGTHVARLENGTHVARLENGTH

45




4

VARLENGTHVARLENGTHVARLENGTHVARLENGTH

36


VARLENGTHVARLENGTHVARLENGTHVARLENGTH

45





Sheet: Sheet1



Excel 2016 (Windows) 32 bit




A


B


C


D


E




1

VARLENGTHVARLENGTHVARLENGTH

=LEN(A1)


={A1&REPT(CHAR(13),MAX(LEN($A$1:$A$4))-LEN(A1))}

=LEN(D1)





Sheet: Sheet1

Edmond
08-16-2019, 10:14 AM
Thank you! This is what I am doing right now with 'lMaxStg - lenStg(i) + 1)'

I checked the length is equal to all the line.

However, now when I realized than passing to word, I cannot manage to change the font format... (range.font.name) does not seem to work....

Edmond
08-16-2019, 11:23 AM
Problem solved. Msgbox doesn't allow monospaced font.

I will figure a way to make it work on Word.

Thank you both for your help!

Edit: I have understood my initial mistake. The rept(max - len) way was a good one. But once on word it didn't work.
I am using the 'Range.Font.Name' property with "Courrier New". I removed one r in the 2 'rr'. of course it worked afterwards...