Merging all outputs into one message box

[DanielPodo]

Hi all, it's been a while since I used this forum but it's that time again where head scratching doesn't help me progress any more.

I've made a simple excel file that has a list of classes, and whether or not a homework is due in. What I wanted to do is create a file that, upon opening, displays a message saying which classes have a homework due in on that day (or whether it's overdue for whatever reason).

Initially, my code was the following:

Sub Workbook_Open()

For Each cell In Sheets("HW").Range("H2:H9")
        If cell.Value = "YES" And cell.Offset(0, -2).Value = "No" Then
            MsgBox cell.Offset(0, -7).Value & " HW is due in today"
            
         ElseIf cell.Value = "OVERDUE" Then
            MsgBox cell.Offset(0, -7).Value & " HW is OVERDUE"
            
         End If
    Next

End Sub

What is going on here is that the macro checks to see if there is a "yes", "no" or "overdue" in the "is it due today" column in my worksheet, and then brings up the corresponding class name with the required text.

However, although it works, it gives me individual message boxes for each message. Is there a simple way of merging all outcomes into one message box? I tried assigning variables and having each iteration output the outcome into that variable, with the final message box being composed of all the required variables...however...that leads to the problem of having an unknown number of variables at the start, because I can't predict how many variables the macro should assign at the start. Or am I thinking about it the wrong way? One other suggestion is that to have the maximum number of variables assigned (as many classes as I teach, if ever the day comes where they all need to hand homework in haha!), and simply have no outcome assigned to a variable if there isn't any corresponding homework due in?

Thanks in advance

[jindon]

Perhaps

Sub Workbook_Open()

For Each cell In Sheets("HW").Range("H2:H9")
        If cell.Value = "YES" And cell.Offset(0, -2).Value = "No" Then
            msg = msg & vbLf & cell.Offset(0, -7).Value & " HW is due in today"
            
         ElseIf cell.Value = "OVERDUE" Then
            msg = msg & vbLf & cell.Offset(0, -7).Value & " HW is OVERDUE"
            
         End If
    Next
    MsgBox IIf(Len(msg),msg,"No message")
End Sub

[Ed_Collins]

Just store all of the messages in a single string variable. Display that variable at the end.
Something like:

Sub Workbook_Open()

answer$ = ""
For Each cell In Sheets("HW").Range("H2:H9")
   If cell.Value = "YES" And cell.Offset(0, -2).Value = "No" Then
       answer$ = answer$ + cell.Offset(0, -7).Value & " HW is due in today"
       answer$ = answer$ + chr$(13)     ' add a carriage return after each line
   ElseIf cell.Value = "OVERDUE" Then
       answer$ = answer$ + cell.Offset(0, -7).Value & " HW is due in today"
       answer$ = answer$ + chr$(13)     ' add a carriage return after each line
   End If
Next

MsgBox answer$
End Sub

(This is untested, but it should work, I think.)

Edit: As usual, someone beats me to it. I think jindon and I had the same idea, however.

[Richard Buttrey]

Hi,

Can you share the workbook with us - anonymised if necessary and manually add some notes that clearly show the text of a message box for a couple of permutations. It seems the maximum number of items to include in the message box is 8, i.e. H2:H9 but then I'm confused by your comment about having an unknown number of variables.

The VBA solution would be to build the message string in the loop and populate the message box after the loop completes.
An alternative, which I generally prefer is to use a cell in the sheet to work out the text of the message box and then read that value into your macro.

[Ed_Collins]

Richard has the same idea too. (The VBA solution would be to build the message string in the loop and populate the message box after the loop completes.)

[DanielPodo]

Hi all, thanks for your inputs.

Jindons solution was what I required, and works perfectly (only one minor alteration to better suit my worksheet a bit better).

I do have a few questions though, as I don't like to remain ignorant of how vba code works.

1. I assume msg is just a variable we've created in this sub. If that's the case, why don't we have to dim it at the start, or at any point in the sub?

2. How can a Len() function be in the Iif condition, when all it does is output a value? There isn't any condition? Or is this a sneaky way of using the Iif function?

[jindon]

I do have a few questions though, as I don't like to remain ignorant of how vba code works.

1. I assume msg is just a variable we've created in this sub. If that's the case, why don't we have to dim it at the start, or at any point in the sub?

Bacause you didn't declare variable for variable "cell".

2. How can a Len() function be in the Iif condition, when all it does is output a value? There isn't any condition? Or is this a sneaky way of using the Iif function?

Len(msg) means, if msg has any string data it returns >0 that is True else 0 that is False.
IIf function plays similar to If worksheet function. so If(Len(msg),msg,"No data")

[Ed_Collins]

Hi Daniel,

Yes, msg as he used it is just a variable. You don't HAVE to dimension your variables. It's just often a good idea and good practice to do so.

The IIF function is used to evaluate an expression and perform one of two actions based on the outcome of the evaluation. The expression in this case that jindon used was LEN(msg). If it's true (meaning msg does contain a length > 0) then display that message, otherwise display "No message."

Another way to code would be something like this:

IF msg = "" then
MsgBox "No message"
ELSE
MsgBox msg
END IF