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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Bill PrewIT / Software Engineering ConsultantCommented:
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 PrewIT / Software Engineering ConsultantCommented:
Kevin,

Glad that was useful, enjoy, and thanks.

~bp
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.