VBA - Dynamic Message Box Display

Experts,

I am trying to build a “summary” to display in a MsgBox utilizing the following logic:

      If TargA is equal to 0, then there is nothing to display

      If TargA is equal to 1, then message should be:
            "THERE IS " & TargA & " CELL NOT MARKED AS 'M' - FOR MEDICARE"  & vbCr & vbNewLine & 

      If TargA is great than 1, then message should be:
            "THERE ARE " & TargA & " CELLS NOT MARKED AS 'M' - FOR MEDICARE" & vbCr & vbNewLine & 

Same basic layout for TargB

      If TargB is 0, then nothing to display

      If TargB is 1, then text is “THERE IS “ & TargB & “CELL WITHOUT A PREMIUM VALUE” & vbCr & vbNewLine & 

      If TargB is great than 1, then “THERE ARE “ & TargB & “CELLS WITHOUT PREMIUM VALUE”

And as long as TargA and/or TargB is great than 0, a final line needs to be added to the msgbox:

      "PLEASE CORRECT THE ABOVE IN GM AND RE-QUERY THE DATA"

If they (TargA and TargB) are both blank, then there were no errors, so the message box should say "Operations successful".

If you run the sub M_Errors on the attached workbook, it runs correctly, but the msgbox is static, displaying results
for both TargA and TargB and the text does not change such as (cell instead of cells), so it "reads" poorly for the end-user.

Is the following code a start in the right direction?

' To Display Message Box or not
If TargA = 0 Then ' skip to TargB
If TargA = 1 Then
    strTargA = "THERE IS " & TargA & " CELL NOT MARKED AS 'M' - FOR MEDICARE"  & vbCr & vbNewLine &
        Else
    strTargA = "THERE ARE " & TargA & " CELLS NOT MARKED AS 'M' - FOR MEDICARE" & vbCr & vbNewLine &

If TargB = 0 Then ' skip to string assembly for message box
If TargB = 1 Then
    strTargB = "THERE IS " & TargB & "CELL WITHOUT A PREMIUM VALUE” & vbCr & vbNewLine &"
        Else
    strTargB = "THERE ARE " & TargB & "CELLS WITHOUT PREMIUM VALUE" & vbCr & vbNewLine &

' Then I want to assemble the above strings, if not null such as:

If strTargA and strTargB = nothing Then
   MsgBox "Operations successful".
   Else MsgBox strTargA + strTargB + "PLEASE CORRECT THE ABOVE IN GM AND RE-QUERY THE DATA"

Open in new window


In the above code, how do I execute my comment " 'skip to TargB":
it is an IF THEN ELSE, If true I want to just continue with TargB, since TargA has not value.

Also, not sure if the results should be a string that is then displayed in the text box?

Ultimately, I want to do this for 6-7 different columns, so I am open to suggestions of other ways to achieve this!

Thank you,
Kevin
EE---VBA---Dynamic-MsgBox---ver-.xls
LVL 23
redrumkevAsked:
Who is Participating?
 
Rory ArchibaldCommented:
PS You can do something like this (making assumptions about your answer!):
' To Display Message Box or not
Select Case TargA
    Case 0
        ' skip to TargB
    Case 1
        strTargA = "THERE IS " & TargA & " CELL NOT MARKED AS 'M' - FOR MEDICARE" & vbCrLf
    Case Else
        strTargA = "THERE ARE " & TargA & " CELLS NOT MARKED AS 'M' - FOR MEDICARE" & vbCrLf
End Select
Select Case TargB
    Case 0 ' skip to string assembly for message box
    Case 1
    strTargB = "THERE IS " & TargB & "CELL WITHOUT A PREMIUM VALUE" & vbCrLf
    Case Else
    strTargB = "THERE ARE " & TargB & " CELLS WITHOUT PREMIUM VALUE" & vbCrLf
End Select
' Then I want to assemble the above strings, if not null such as:

If TargA + TargB = 0 Then
   MsgBox "Operations successful"
Else
   MsgBox strTargA + strTargB + "PLEASE CORRECT THE ABOVE IN GM AND RE-QUERY THE DATA"
End If

Open in new window

0
 
Rory ArchibaldCommented:
Is the end result intended to be one messagebox with the complete appended message for each check in it, or do you do one check, and message then exit if failed, otherwise move on to the next check, etc?
0
 
redrumkevAuthor Commented:
rorya,

I want to do one message box with the complete appended maessage in it at the end that lists 6 or 7 things. Such as:

Item A had 3 errors

Item B had 4 errors

Item C had 1 error

Item E had 12 errors

Item F had 1 error

* Notice I left Item D out, as item D had no errors.

I can review the above code in a bit, thank you for the quick reply!

Kevin

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Bill PrewCommented:
You'll either like this, or you won't but figured I'd share a slightly different approach that might be easier to expand further.  Basically you define the templates for the various error messages at the top, with both a single form, and a plural form, and a place holder of <N> that will be replaced by the count found.  Then we use a small function to format the error messages.  The rest should be fairly straight forward, but let me know if it doesn't make sense.

strMsgA1 = "THERE IS <N> CELL NOT MARKED AS 'M' - FOR MEDICARE" & vbCrLf
strMsgA2 = "THERE ARE <N> CELLS NOT MARKED AS 'M' - FOR MEDICARE" & vbCrLf
strMsgB1 = "THERE IS <N> CELL WITHOUT PREMIUM VALUE" & vbCrLf
strMsgB2 = "THERE ARE <N> CELLS WITHOUT PREMIUM VALUE" & vbCrLf

TargA = 1
TargB = 2

strResults = ""
strResults = strResults & BuildMsg(TargA, strMsgA1, strMsgA2)
strResults = strResults & BuildMsg(TargB, strMsgB1, strMsgB2)

If strResults = "" Then
   strResults = "Operations successful."
Else 
   strResults = strResults & vbCrLf & "PLEASE CORRECT THE ABOVE IN GM AND RE-QUERY THE DATA."
End If
MsgBox strResults

Function BuildMsg(intCode, strMsg1, strMsg2)
   If intCode > 0 Then
       If intCode > 1 Then
           BuildMsg = Replace(strMsg2, "<N>", intCode)
       Else
           BuildMsg = Replace(strMsg1, "<N>", intCode)
       End If
   End If
End Function

Open in new window

~bp
0
 
redrumkevAuthor Commented:
Rorya,

This is a great solution, I have not used the select case feature in VBA and have wanted to try it out, but never came up with an instance where I thought it would make sense. So I am going to utilize this, which is working well, thank you!

Billpew,

I like your solution, because the string values are really clear, being laid out first. I can copy and paste these, just changing the singular and plural text to meet my needs. This would be the format I am most use to, however it is not something I would have come up with.

In the end - they both worked quickly. I gave Rorya more points for being first.

Thank you to each of you for your help!!!
Kevin
0
 
Bill PrewCommented:
Kevin,

Glad that was useful, enjoy, and thanks.

~bp
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.