Solved: MsgBox not big enough?

[jwise]

I found some code on j-walk that built multi-line messages that were displayed via MsgBox. Here is an example:
Dim msg1 as String
...
msg1 = "This is a message" & vbCrLf
msg1 = msg1 & "This is a second line" & vbCrLf
...
MsgBox msg1
...


There must be some kind of limit as to the number of lines this works for, or perhaps the limit is based on the number of characters. If I switch to a userform, then the problem becomes "How do I build a dynamic length userform?" What's the best way to handle this?

[lucas]

Why would it need to be dynamic in length? Is the length of the message going to change?

[unmarkedhelicopter]

if it does you cater for a reasonable size, and then add scoll bars

[Bob Phillips]

For a userform use code like this, where Label1 is a label control that will display the message



Public MESSAGE As String

Private Sub UserForm_Activate()
Dim nVariable As Double

nVariable = 12 * ((Len(MESSAGE) - Len(Replace(MESSAGE, Chr(10), ""))) / 2 + 1)
Me.Label1.Caption = MESSAGE
Me.Height = 82 + nVariable
End Sub


You would call it like so



Sub TestMsgBox()
With UserForm1
.MESSAGE = "This is a message" & vbCrLf & _
"This is a second line" & vbCrLf
.Show
End With


With UserForm1
.MESSAGE = "This is a message" & vbCrLf & _
"This is a second line" & vbCrLf & _
"and a third line" & vbCrLf & _
"and a foiurth line" & vbCrLf
.Show
End With

End Sub

[jwise]

I apologize for not explicitly stating that it is indeed variable length. This is a precursor to what I hope will be an add-in at some point, and the length depends on the number of macros in the active workbook. I am displaying information (three lines per macro now) on each macro. This may change. The particular test case I chose has about 15 macros in "Module1". It was chosen for this reason.

The ideal solution would be some sort of scroll window where the user could scroll this information up and down. This way a user could look at their convenience for the pertinent data.

I've never seen any scroll window code, so I'll be searching for some examples.

Thanks to all responders.

[unmarkedhelicopter]

Sorry I thought somebody had mentioned it, which was why my ealier reply was so cryptic.
Use a text box, set multiline to true and set vertical scroll bars.

[Ivan F Moala]

If you must use a Msgbox then see here

http://www.xcelfiles.com/Msgbox1024.html

[mikerickson]

Scrolling up and down sounds like a List Box.

[unmarkedhelicopter]

Have you ever tried populating a list box with continuous text ???

[mikerickson]

No, I haven't, for good reason. The talk of scrolling to look for information confused my thinking.

[Zack Barresse]

I'm with mikerickson on this one. Use a ListBox with a column count of 3 (for 3 items per record/macro as you stated), one line for each macro info. The List property of a ListBox is easily set with an array.

[jwise]

Thanks for the advice. I can't make up my mind as to which solution to choose. This is a very important part of the user interface, so I'll continue to muddle through the suggested options until I can see a "best" solution.

Thanks again.

[MWE]

I played around with several approaches to the 1024 (or so) character limitation in MsgBox. I found a few UserForm versions and wrote one myself. But what I ended up using most of the time is a procedure that looks like MsgBox but actually buffers the text into reasonable blocks and calls MsgBox repeatedly. I called it MsgBox2 (pretty slick name, hug) and posted it to the KB ... http://vbaexpress.com/kb/getarticle.php?kb_id=703

[jwise]

I looked at your code and copied it to my machine. Thanks.

I will evaluate and see if it will handle my situation. The problem still lingers out there. Luckily, there are more pieces to put in place before I need the "report" piece to display the information I've gathered.

Thanks again for the suggestion and the working code.