PDA

View Full Version : Solved: MsgBox not big enough?



jwise
11-20-2007, 08:29 AM
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
11-20-2007, 09:09 AM
Why would it need to be dynamic in length? Is the length of the message going to change?

unmarkedhelicopter
11-20-2007, 09:10 AM
if it does you cater for a reasonable size, and then add scoll bars

xld
11-20-2007, 09:17 AM
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
11-20-2007, 09:32 AM
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
11-20-2007, 11:01 AM
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
11-20-2007, 12:30 PM
If you must use a Msgbox then see here

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

mikerickson
11-20-2007, 06:06 PM
Scrolling up and down sounds like a List Box.

unmarkedhelicopter
11-21-2007, 02:26 AM
Have you ever tried populating a list box with continuous text ???

mikerickson
11-21-2007, 08:35 AM
No, I haven't, for good reason. The talk of scrolling to look for information confused my thinking.

Zack Barresse
11-21-2007, 12:30 PM
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
11-29-2007, 02:22 PM
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
11-29-2007, 03:35 PM
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
11-29-2007, 03:59 PM
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.