syntax for code in MsgBox()?

[Ouka]

Hi all,

I was wondering if it was possible to put code inside the MsgBox object
that would dictate what info the messagebox would display?

Description of project:

I have a userform that allows for the seriel entry of date, name, sex

This information is dumped into a hidden sheet where it is sorted on
date (after clearing the columns of previously entered data)

I then have a loop that determines how many entries there are:

Dim M as Integer
Dim trmLoopsSize as Integer

Do
trmLoopSize = M
M = M + 1
Loop Until Woorksheets("Hidden1").Cells(M, "H").value = ""



Now, I can get each of the rows of data to output into their own
message box with the code:

For M = 1 to trmLoopSize
MsgBox "Please review this data for entry: " & chr$(13) _
& chr$(13) _
& Worksheets("Hidden1").Cells(M, "H").Value _
& " " _
& Worksheets("Hidden1").Cells(M, "I").Value _
& " " _
& Worksheets("Hidden1").Cells(M, "J").Value _
& Chr$(13)
Next M


However, what I really want to happen is to have all that info in a
single message box, but I can't seem to get the syntax right.

Any help would be most appreciated.

PS - how do you get indented text to work in a vB forum other than
masking text placeholders with the background color?

 

[Henry]

Ouka,

Why not design a form that looks like a message box?
You can then open it with whatever data you want in it.

In a General Module

Public Sub MyMsgSub(MyMessage, MyType, MyTitle)
'*****************************************
'UDF to show my message box
'*****************************************
MyMsgBox.Caption = MyTitle 'Set form title
MyMsgBox.Label.Caption = MyMessage 'Set label text
Select Case MyType 'Choose what type of box
Case 1 'Blue background with OK
button only
MyMsgBox.OKbtn.Visible = True
MyMsgBox.NoBtn.Visible = False
MyMsgBox.YesBtn.Visible = False
Case 2 'Blue background with
Yes/No buttons
MyMsgBox.OKbtn.Visible = False
MyMsgBox.NoBtn.Visible = True
MyMsgBox.YesBtn.Visible = True
Case 11 'Red background with OK
button
MyMsgBox.BackColor = RGB(255, 0, 0)
MyMsgBox.Label.BackColor = RGB(255, 0, 0)
MyMsgBox.OKbtn.Visible = True
MyMsgBox.NoBtn.Visible = False
MyMsgBox.YesBtn.Visible = False
Case 12 'Red background with
Yes/No buttons
MyMsgBox.BackColor = RGB(255, 0, 0)
MyMsgBox.Label.BackColor = RGB(255, 0, 0)
MyMsgBox.OKbtn.Visible = False
MyMsgBox.NoBtn.Visible = True
MyMsgBox.YesBtn.Visible = True

End Select
MyMsgBox.Show
End Sub

and call it with
Call MyMsgSub("Your message",Your type,"Your title")

In the OKBtn_Click() event
Unload Me


Henry

 

[Ouka]

Well, I pretty much ended up doing that anyway, only I made the form at
design time instead of run time. The code was too complicated for me
to deal with trying to do it at run time.

The form has 60 label fields arranged in 3 columns. The rows are
filled in with information collected from the previous userform.

The reason I wanted to use a MsgBox is because it would resize itself
dynamically at runtime depending on how many entries the user put in.
With the userform it's a static size so it looks kind of ugly if there
was only 1 entry made by the user on the previous form.

I may revist the form when the enitre app is actually functioning and
make 4 different templates based on a Case selection on the number of
entries, but for now I'll live with the ugluness unless somone can show
me how to put code into a MsgBox that would dynamically change the
output during runtime.

 

[Patti]

Ouka,

Maybe something like this:

(If your not already putting Option Explicit at the top of each module, you
should do so - you dimmed "trmLoopsSize" but used "trmLoopsize" (one less S)
in your code.)

Regards,

Patti
--------------
Sub tester()

Dim M As Integer
Dim trmLoopsSize As Integer
Dim strMsg As String

strMsg = "Please review this data for entry: " & Chr$(13) _
& Chr$(13)

Do
trmLoopsSize = M
M = M + 1
Loop Until Worksheets("Hidden1").Cells(M, "H").Value = ""
Debug.Print trmLoopsSize

For M = 1 To trmLoopsSize
strMsg = strMsg & Worksheets("Hidden1").Cells(M, "H").Value & " " _
& Worksheets("Hidden1").Cells(M, "I").Value & " " _
& Worksheets("Hidden1").Cells(M, "J").Value _
& Chr$(13)
Next M
MsgBox strMsg
End Sub