MessageBox line length

[nikki333]

Hi Folks

Since there is no option to format message boxes, I was wondering if there if a way to find the maximum number of characters before Excel does a line break in a messagebox. Apparently that number varies depending on the excel version and/or screen resolution, but I'm not sure about that. I can only tell that the number of characters for a single line varies depending on the screen and/or office version. For example, oddly enough, the standard company screen (i.e. around 19", Win8, Excel 2013) shows less line breaks than my personal PC (Win10, Excel 2016, and high resolution) for the same message.

The reason for that is that I'd like to set the length of a splitter line (consisting of underscores) in order to make a larger, multi-line message more readable.

Any ideas?

[Rob342]

Hi nikki333

max no per line = 85
Maxium amount of chars = 1024
That depends on sixe of chars as well so might be less

[nikki333]

thank you Rob

Using the standard font setting, i got a break at 79 -------

[Rob342]

No problem
Have you thought about creating a user form with a textbox, then you can format it the way you want.

[Paul_Hossler]

I had to do a couple of fairly large applications and wanted the message boxes to look 'special'

You can look at the attached which use a user form as a display, but I added options, etc to avoid re-inventing the wheel and to keep things looking the same

You could customize the user form using the standard UF tools to add a logo or lables, etc.

Capture.JPG

Option Explicit
Sub test()
    
    'the ^ equals vbLfCr and the ` equals vbCLf & vbTab
    Call msgInformation("AAAA bbbbbbbbbbbbbb cccccccccccccccc^^ddddd eeeeeeeeeeeee fffffffff`11111111`2222222`3333333")
    Call msgWarning("AAAA bbbbbbbbbbbbbb cccccccccccccccc^^ddddd eeeeeeeeeeeee fffffffff`11111111`2222222`3333333")
    Call msgCritical("AAAA bbbbbbbbbbbbbb cccccccccccccccc^^ddddd eeeeeeeeeeeee fffffffff`11111111`2222222`3333333")


    'vbYes = 6, vbNo = 7
    MsgBox msgContinueOrNot("AAAA bbbbbbbbbbbbbb cccccccccccccccc^^ddddd eeeeeeeeeeeee fffffffff`11111111`2222222`3333333")
    MsgBox msgContinueOrNot("AAAA bbbbbbbbbbbbbb cccccccccccccccc^^ddddd eeeeeeeeeeeee fffffffff`11111111`2222222`3333333", False)
    

    'vbYes = 6, vbNo = 7
    MsgBox msgYesOrNo("AAAA bbbbbbbbbbbbbb cccccccccccccccc^^ddddd eeeeeeeeeeeee fffffffff`11111111`2222222`3333333")
    MsgBox msgYesOrNo("AAAA bbbbbbbbbbbbbb cccccccccccccccc^^ddddd eeeeeeeeeeeee fffffffff`11111111`2222222`3333333", False)

    'vbAbort = 3, vbRetry = 4, vbIgnore = 5
    MsgBox msgAbortRetryIgnore("AAAA bbbbbbbbbbbbbb cccccccccccccccc^^ddddd eeeeeeeeeeeee fffffffff`11111111`2222222`3333333")
    MsgBox msgAbortRetryIgnore("AAAA bbbbbbbbbbbbbb cccccccccccccccc^^ddddd eeeeeeeeeeeee fffffffff`11111111`2222222`3333333", vbRetry)
    MsgBox msgAbortRetryIgnore("AAAA bbbbbbbbbbbbbb cccccccccccccccc^^ddddd eeeeeeeeeeeee fffffffff`11111111`2222222`3333333", vbIgnore)


    'example of real world
    If msgYesOrNo("File not there!!!^^Keep on going?") = vbNo Then
        MsgBox "OK, stopping"
    Else
        MsgBox "OK, I'll look for it"
    End If
    
End Sub