PDA

View Full Version : Combining MsgBoxes into one box



christopherw
08-08-2012, 07:06 AM
Could someone help me combine these into one Msgbox? I have one message box that has multiple lines that I joined with the & vbNewLine &, but I want the lcount to be included and I can't figured out how.

Thanks in advance for the help.

MsgBox ("• Count of Lines: " & Worksheets("All").Evaluate("Countif(A:A,""*"")-1") & vbNewLine & ("• Count of Lines on Hold: " & Worksheets("Holds").Evaluate("Countif(A:A,""*"")-1") & vbNewLine & ("• Count of Lines on CSR Hold: " & Worksheets("CSR Holds").Evaluate("Countif(A:A,""*"")-1") & vbNewLine & ("• Count of Z3/Z4 Holds: " & Worksheets("Holds").Evaluate("Countif(H:H,""Z3"")+Countif(H:H,""Z4"")") & vbNewLine & ("• Count of 12, DS & ED Edit Holds: " & Worksheets("Holds").Evaluate("Countif(H:H,""12"")+Countif(H:H,""DS"")+Countif(H:H,""ED"")")))))) Dim l As Long Dim lCount As Long lCount = 0 For l = 1 To Sheets("Holds").UsedRange.Rows.Count If Sheets("Holds").Cells(l, 1).Font.color = 0 And Sheets("Holds").Cells(l, 1).Interior.color = 5296274 Then lCount = lCount + 1 Next l MsgBox ("Total Lines Within 15 Business Days of Production Date: " & lCount) lCount = 0 For l = 1 To Sheets("Holds").UsedRange.Rows.Count If Sheets("Holds").Cells(l, 1).Font.color = 192 And Sheets("Holds").Cells(l, 1).Interior.color = 5296274 Then lCount = lCount + 1 Next l MsgBox ("Total Lines Within 7 Business Days of Production Date: " & lCount) lCount = 0 For l = 1 To Sheets("Holds").UsedRange.Rows.Count If Sheets("Holds").Cells(l, 1).Font.color = 0 And Sheets("Holds").Cells(l, 1).Interior.color = 49407 Then lCount = lCount + 1 Next l MsgBox ("Total Lines Within 10 Day Critical Window: " & lCount) lCount = 0 For l = 1 To Sheets("Holds").UsedRange.Rows.Count If Sheets("Holds").Cells(l, 1).Font.color = 192 And Sheets("Holds").Cells(l, 1).Interior.color = 49407 Then lCount = lCount + 1 Next l MsgBox ("Total Lines Within 4 Day Critical Window: " & lCount)

Kenneth Hobs
08-08-2012, 07:23 AM
Build the string for it first. You should build it in steps rather than one long line. In that way, if something goes wrong, it is easier to troubleshoot. Use F8 to step through the code to see how each part evaluates.

The usual rule is to add an extra quote when you want to embed a quote. Another easy method is to set a variable like q= """" and concatenate q for each embedded quote that you need.

When posting code, if it all pastes to one line, paste to a range and then cut and paste that. The forum can be funny like that sometimes.