Display multiple message boxes in just one box

Question

I have a Macro to check if data satisfies some conditions. If it does one message box is displayed for every matched criteria.

I would like to have a unique Message Box displaying all conditions which were matched.

What is the best approach to tackle this issue? The below example is what I've got so far.

N   I   Esp             Par     T   DBH     H_m     Cod
2   111 E_cit           432     1                   1
2   111 E_cit           432     2                   1
2   111 E_cit           432     3                   1
2   111 E_cit           432     4   3       17.4    
2   111 E_cit           432     5                   
2   111 E_cit           432     6   14.48   15

The Macro (with two conditions as examples):

Option Explicit

Dim DBH As Range
Dim Cod As Range

Sub Conditions_1()

'------------------------------------------------
'DBH should be between 5 and 45 cm

    For Each DBH In Range("F2:F7")
        If DBH < 5 And DBH <> 0 Or DBH > 45 And DBH <> 0 Then
        MsgBox "1 - DBH is not between 5 cm and 45 cm"
            Exit For
        End If
    Next DBH

'------------------------------------------------
'There can't be an empty cell on DBH column 
'together with an empty cell on the Code column

    For Each DBH In Range("F2:F7")
        If DBH = 0 And DBH.Offset(, 2) = 0 Then
        MsgBox "2 - DBH is empty and also the Code"
            Exit For
        End If
    Next DBH

 End Sub

This current code yields two message boxes separately,

"1- DBH is not between 5 cm and 45 cm"
"2- DBH is empty and also the Code"

but, I'd like to have a single box listing all conditions once. Any help is appreciated.

Also note that that part of your conditions are redundant. `DBH <> 0` in `DBH > 45 And DBH <> 0` will always be true if `DBH > 45`. — Cor_Blimey, May 08 '14 at 21:08

Tim Williams · Accepted Answer · 2014-05-08T22:11:05.017

1

Option Explicit

Dim DBH As Range
Dim Cod As Range


Sub Conditions_1()

Dim msg as string

msg=""

'------------------------------------------------
'DBH should be between 5 and 45 cm

    For Each DBH In Range("F2:F7")
        If DBH < 5 And DBH <> 0 Or DBH > 45 And DBH <> 0 Then
            msg = msg & "1 - DBH in " & DBH.Address() & _
                        " is not between 5 cm and 45 cm" & vbLf
        End If
    Next DBH

'------------------------------------------------
'There can't be an empty cell on DBH column 
'together with an empty cell on the Code column

    For Each DBH In Range("F2:F7")
        If DBH = 0 And DBH.Offset(, 2) = 0 Then
            msg = msg & "2 - DBH in " & DBH.Address() & _
                        " is empty and also the Code" & vbLf
        End If
    Next DBH

    If Len(msg) > 0 Then MsgBox msg


End Sub

edited May 08 '14 at 22:11

answered May 08 '14 at 21:05

Tim Williams

154,628
8
97
125

The above will have a long message of perhaps several "1 - DBH etc" and "2 - DBH etc" if multiple cells in the range pass the criteria. My reading is it should be shown just once. A way I think that covers this is to have a Exit For inside the If just below the msg = "..." line – Cor_Blimey May 08 '14 at 21:16
1

I like to show all problems in a single message, so the user does have to re-run the routine only to find they only fixed one of the issues! (and then keep re-running until there's no messagebox...) I will make a quick edit to make the (possibly longish) message more useful. It would also help to reduce any redundancy in your checking routine. – Tim Williams May 08 '14 at 22:09
@TimWilliams that makes sense. A count alongside each error message is an idea. Anyway, nice answer – Cor_Blimey May 09 '14 at 15:50

Display multiple message boxes in just one box

1 Answers1