Consulting

Results 1 to 12 of 12

Thread: Justify text in a msgbox

  1. #1
    VBAX Regular
    Joined
    Oct 2018
    Posts
    43
    Location

    Justify text in a msgbox

    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:

    Capture.PNG

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

    Would someone know how I can achieve a proper column?

    thanks a lot!
    Edmond

  2. #2
    VBAX Expert Leith Ross's Avatar
    Joined
    Oct 2012
    Location
    San Francisco, California
    Posts
    552
    Location
    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.
    Sincerely,
    Leith Ross

    "1N73LL1G3NC3 15 7H3 4B1L17Y 70 4D4P7 70 CH4NG3 - 573PH3N H4WK1NG"

  3. #3
    VBAX Regular
    Joined
    Oct 2018
    Posts
    43
    Location
    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...

    Capture2.PNG
    Edmond

  4. #4
    VBAX Expert Leith Ross's Avatar
    Joined
    Oct 2012
    Location
    San Francisco, California
    Posts
    552
    Location
    Hello Edmond,

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

    "1N73LL1G3NC3 15 7H3 4B1L17Y 70 4D4P7 70 CH4NG3 - 573PH3N H4WK1NG"

  5. #5
    Capture.JPG

    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

  6. #6
    VBAX Regular
    Joined
    Oct 2018
    Posts
    43
    Location
    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.
    Edmond

  7. #7
    VBAX Expert Leith Ross's Avatar
    Joined
    Oct 2012
    Location
    San Francisco, California
    Posts
    552
    Location
    Hello KOKOSEK,

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

    "1N73LL1G3NC3 15 7H3 4B1L17Y 70 4D4P7 70 CH4NG3 - 573PH3N H4WK1NG"

  8. #8
    VBAX Expert Leith Ross's Avatar
    Joined
    Oct 2012
    Location
    San Francisco, California
    Posts
    552
    Location
    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.
    Sincerely,
    Leith Ross

    "1N73LL1G3NC3 15 7H3 4B1L17Y 70 4D4P7 70 CH4NG3 - 573PH3N H4WK1NG"

  9. #9
    VBAX Regular
    Joined
    Oct 2018
    Posts
    43
    Location
    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...
    Edmond

  10. #10
    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

  11. #11
    VBAX Regular
    Joined
    Oct 2018
    Posts
    43
    Location
    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

  12. #12
    VBAX Regular
    Joined
    Oct 2018
    Posts
    43
    Location
    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...
    Last edited by Edmond; 08-16-2019 at 11:39 AM.
    Edmond

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •