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?
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
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"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
Rory Archibald
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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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
Kevin,
Glad that was useful, enjoy, and thanks.
~bp
Glad that was useful, enjoy, and thanks.
~bp