advice for msgbox summary(single macro run on multiple sheets)

[jake.peterson]

I created a form to capture information for employee transitions. Based on the transition type I require certain fields to be completed. I run the same code on as many as 5 sheets in the same workbook(could be mroe in the future, I only built 5 for now).

What I want to do is cycle through the sheets BeforeSave and display a msgbox for each sheet that highlights any missing information and give the user the option to cancel the save process and edit or continue with an incomplete form. I use "G21" to determine if the sheet was used as all transitions hinge on the value in G21. Here's what I have so far:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim TType As String
Dim ws As Worksheet
Dim S_date, G_live, Candidate, Cube, Division, New_title, Role_change, LOA_date, Return_date, Term_date, Term_type, Notes As String
Dim msg As String
 
For Each ws In ThisWorkbook.Worksheets
    'select sheet so user can view when msgbox pops
    ws.Select
    'check to see if sheet was used
    If Range("g21") <> "" Then
    TType = ws.Range("G21").Value
    
        Select Case TType
            Case "New Hire (ISR)"
                If ws.Range("d27") = "" Then S_date = "Start Date with Inside Sales"
                If ws.Range("d29") = "" Then G_live = "Go Live Date on Phone"
                If ws.Range("d33") = "" Then Candidate = "Candidate Type"
                If ws.Range("d35") = "" Then Cube = "Cube Location"
                
            Case "New Hire with Territory (RIC)"
                If ws.Range("d27") = "" Then Sdate = "Start Date with Inside Sales"
                If ws.Range("d29") = "" Then G_live = "Go Live Date on Phone"
                If ws.Range("d33") = "" Then Candidate = "Candidate Type"
                If ws.Range("d39") = "" Then Division = "Inside Sales Division"
               
             Case "Role Change with Territory(RIC)"
                If ws.Range("d35") = "" Then Cube = "Cube Location"
                If ws.Range("d39") = "" Then Division = "Inside Sales Division"
                
             Case "Termination"
                If ws.Range("i41") = "" Then Term_type = "Termination Type"
                If ws.Range("i43") = "" Then Term_date = "Termination Date"
                
              Case "Leave of Absense"
                If ws.Range("i34") = "" Then LOA_date = "LOA Effective Date"
                If ws.Range("i36") = "" Then Return_date = "Date of Expected Return"
                
              Case "Other"
                If ws.Range("c51") = "" Then Notes = "Manager Notes"
    
        'msgbox list each missing piece of information
        'ie "Missing Required Information:
        '   Start Date with inside sales
        '   Candidate Type
        '   Continue and save or cancel?

'set variables back to "" before next sheet   
'    S_date = ""
'    G_live = ""
'    Candidate = ""
'    Cube = ""
'    Division = ""
'    New_title = ""
'    Role_change = ""
'    LOA_date = ""
'    Return_date = ""
'    Term_date = ""
'    Term_type = ""
'    Notes = ""
    
    
        End Select
    End If
Next ws
End Sub

So a few questions:
1) How can i cycle though a specified number(5 for now) of sheets in "for each ws in ThisWorkbook"?
2) What is the best way to compile the message for missing information?

Thank you for taking a look.

 

[sijpie]

Well your code is doing fine for cycling through each book.

And here is one way to compile the msgbox

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim TType As String
Dim ws As Worksheet
Dim S_date, G_live, Candidate, Cube, Division, New_title, Role_change, LOA_date, Return_date, Term_date, Term_type, Notes As String
Dim msg As String
Dim mbResult As VbMsgBoxResult
 
For Each ws In ThisWorkbook.Worksheets
    'select sheet so user can view when msgbox pops
    ws.Select
    'check to see if sheet was used
    If Range("g21") <> "" Then
    TType = ws.Range("G21").Value
    
        Select Case TType
            Case "New Hire (ISR)"
                If ws.Range("d27") = "" Then S_date = "Start Date with Inside Sales"
                If ws.Range("d29") = "" Then G_live = "Go Live Date on Phone"
                If ws.Range("d33") = "" Then Candidate = "Candidate Type"
                If ws.Range("d35") = "" Then Cube = "Cube Location"
                
            Case "New Hire with Territory (RIC)"
                If ws.Range("d27") = "" Then S_date = "Start Date with Inside Sales"
                If ws.Range("d29") = "" Then G_live = "Go Live Date on Phone"
                If ws.Range("d33") = "" Then Candidate = "Candidate Type"
                If ws.Range("d39") = "" Then Division = "Inside Sales Division"
               
             Case "Role Change with Territory(RIC)"
                If ws.Range("d35") = "" Then Cube = "Cube Location"
                If ws.Range("d39") = "" Then Division = "Inside Sales Division"
                
             Case "Termination"
                If ws.Range("i41") = "" Then Term_type = "Termination Type"
                If ws.Range("i43") = "" Then Term_date = "Termination Date"
                
              Case "Leave of Absense"
                If ws.Range("i34") = "" Then LOA_date = "LOA Effective Date"
                If ws.Range("i36") = "" Then Return_date = "Date of Expected Return"
                
              Case "Other"
                If ws.Range("c51") = "" Then Notes = "Manager Notes"
        End Select
    
        'msgbox list each missing piece of information
        'ie "Missing Required Information:
        '   Start Date with inside sales
        '   Candidate Type
        '   Continue and save or cancel?
        mbResult = MsgBox("Missing Information:" & vbCrLf & _
            IIf(Len(S_date), S_date & vbCrLf, "") & _
            IIf(Len(G_live), G_live & vbCrLf, "") & _
            IIf(Len(Candidate), Candidate & vbCrLf, "") & _
            IIf(Len(Cube), Cube & vbCrLf, "") & _
            IIf(Len(Division), Division & vbCrLf, "") & _
            IIf(Len(Term_type), Term_type & vbCrLf, "") & _
            IIf(Len(Term_date), Term_date & vbCrLf, "") & _
            IIf(Len(LOA_date), LOA_date & vbCrLf, "") & _
            IIf(Len(Return_date), Return_date & vbCrLf, "") & _
            IIf(Len(Notes), Notes & vbCrLf, "") & _
            vbCrLf & vbCrLf & "Please press Cancel to correct prior to save" & _
            vbCrLf & "or OK to continue save & close.", vbOKCancel, "Missing information")
       ' set variables back to "" before next sheet
            S_date = ""
            G_live = ""
            Candidate = ""
            Cube = ""
            Division = ""
            New_title = ""
            Role_change = ""
            LOA_date = ""
            Return_date = ""
            Term_date = ""
            Term_type = ""
            Notes = ""
    
        If mbResult = vbCancel Then
 '           Cancel = True
            Exit Sub
        End If
    End If
Next ws
End Sub

 

[sijpie]

Also I suggest that at the top of any code module you write

Option Explicit

That will make VBA check that all your variables have been declared, as you had a typo in one variable.

(You can have this done automatically by setting Tools/Options../Editor - Require Variable Declaration)

 

[jake.peterson]

Thank you sijpie. That worked perfectly. I also added the option explicit as you suggested. It sounds like a good way to catch typos.

Did you have any ideas for limiting the number of times the macro cycles? I have a summary tab at the end that I don't want to check, so I was hoping I could somehow limit the number of times the For Each loop runs.

Thanks again!

 

[sijpie]

just do a check in your loop:

For Each ws In ThisWorkbook.Worksheets
    'select sheet so user can view when msgbox pops
    '' ws.Select  ' don't need this select really - saves time
    If ws.Name <> "Summary" Then
        'check to see if sheet was used
        If Range("g21") <> "" Then
...


    End If
Next ws

 

[jake.peterson]

Thanks again sijpie! I used ws.select so the sheet with missing info pops up. I would still like to switch to the sheet with missing info but maybe I can place it before the msgbox code so it only selects the sheet when there is missing info.

Thanks

 

[sijpie]

Going through the code, I see that the sheet is selected early on, so it should be visible when the messagbox pops up, but i have moved it to a more logical position. However, I also notice that the messagebox will also pop-up if everything is OK, so we need to check if something is missing and then show the pop-up only if the flag is set. I have done this by first compiling most of the error message and checking its length.

Also in your declarations I notice you have a whole lot of strings , but you only declare the last one as string:

Dim S_date, G_live, Candidate, Cube, Division, New_title, Role_change, LOA_date, Return_date, Term_date, Term_type, Notes As String

This has the result that all will be of Variant type, only Notes will be String type. (This is different than when declaring in C, or C++)
Personally I will precede my variables with a letter (or two) indicating the type.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim ws As Worksheet
Dim sTType As String, sS_Date As String, sG_live As String, sCandidate As String, sCube As String, _
    sDivision As String, sNew_title As String, sRole_change As String, sLOA_date As String, _
    sReturn_date As String, sTerm_date As String, sTerm_type As String, sNotes As String, sMsg As String
Dim mbResult As VbMsgBoxResult
 
For Each ws In ThisWorkbook.Worksheets
    'check to see if sheet was used
    If Range("g21") <> "" Then
    sTType = ws.Range("G21").Value
    
        Select Case sTType
            Case "New Hire (ISR)"
                If ws.Range("d27") = "" Then sS_Date = "Start Date with Inside Sales"
                If ws.Range("d29") = "" Then sG_live = "Go Live Date on Phone"
                If ws.Range("d33") = "" Then sCandidate = "sCandidate Type"
                If ws.Range("d35") = "" Then sCube = "sCube Location"
                
            Case "New Hire with Territory (RIC)"
                If ws.Range("d27") = "" Then sS_Date = "Start Date with Inside Sales"
                If ws.Range("d29") = "" Then sG_live = "Go Live Date on Phone"
                If ws.Range("d33") = "" Then sCandidate = "sCandidate Type"
                If ws.Range("d39") = "" Then sDivision = "Inside Sales sDivision"
               
             Case "Role Change with Territory(RIC)"
                If ws.Range("d35") = "" Then sCube = "sCube Location"
                If ws.Range("d39") = "" Then sDivision = "Inside Sales sDivision"
                
             Case "Termination"
                If ws.Range("i41") = "" Then sTerm_type = "Termination Type"
                If ws.Range("i43") = "" Then sTerm_date = "Termination Date"
                
              Case "Leave of Absense"
                If ws.Range("i34") = "" Then sLOA_date = "LOA Effective Date"
                If ws.Range("i36") = "" Then sReturn_date = "Date of Expected Return"
                
              Case "Other"
                If ws.Range("c51") = "" Then sNotes = "Manager sNotes"
        End Select
    
        'Msgbox list each missing piece of information
        'ie "Missing Required Information:
        '   Start Date with inside sales
        '   sCandidate Type
        '   Continue and save or cancel?
        sMsg = IIf(Len(sS_Date), sS_Date & vbCrLf, "") & _
            IIf(Len(sG_live), sG_live & vbCrLf, "") & _
            IIf(Len(sCandidate), sCandidate & vbCrLf, "") & _
            IIf(Len(sCube), sCube & vbCrLf, "") & _
            IIf(Len(sDivision), sDivision & vbCrLf, "") & _
            IIf(Len(sTerm_type), sTerm_type & vbCrLf, "") & _
            IIf(Len(sTerm_date), sTerm_date & vbCrLf, "") & _
            IIf(Len(sLOA_date), sLOA_date & vbCrLf, "") & _
            IIf(Len(sReturn_date), sReturn_date & vbCrLf, "") & _
            IIf(Len(sNotes), sNotes & vbCrLf, "")
        If Len(sMsg) Then
            'select sheet so user can view when Msgbox pops
           ws.Select
           mbResult = MsgBox("Missing Information:" & vbCrLf & sMsg & _
               vbCrLf & vbCrLf & "Please press Cancel to correct prior to save" & _
               vbCrLf & "or OK to continue save & close.", vbOKCancel, "Missing information")
          ' set variables back to "" before next sheet
               sS_Date = ""
               sG_live = ""
               sCandidate = ""
               sCube = ""
               sDivision = ""
               sNew_title = ""
               sRole_change = ""
               sLOA_date = ""
               sReturn_date = ""
               sTerm_date = ""
               sTerm_type = ""
               sNotes = ""
       
           If mbResult = vbCancel Then
               Cancel = True    ' Now the save does not go ahead
               Exit Sub
           End If
        End If
    End If
Next ws
End Sub

 

[jake.peterson]

Thanks again. I noticed a few things that needed to be fixed yesterday as well. When I was referenceing range("g21") it only referenced the value on the active sheet. so if say sheet 4 was active when I hit save it would continually look at g21 on sheet 4. Changed that reference to ws.range("g21") which cleared that up.

I aslo noticed the msgbox would pop on each sheet even when there was no missing info. I added a boolean value to check to see if the length of each string was >1, then build the message based on the boolean value.

Here's what i ended up with. I'll go with your code because it makes more sense. This was just my amature way of getting around the problem.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim TType As String
Dim ws As Worksheet
Dim S_date, G_live, Candidate, Cube, Division, New_title, Role_change, LOA_date, Return_date, Term_date, Term_type, Notes As String
Dim msg As Boolean
Dim mbResult As VbMsgBoxResult
 
For Each ws In ThisWorkbook.Worksheets
    If ws.Name <> "For Database" Then
    'check to see if sheet was used
       If ws.Range("g21") <> "" Then
           TType = ws.Range("G21").Value
           
               Select Case TType
                    Case "New Hire (ISR)"
                       If ws.Range("d27") = "" Then S_date = "Start Date with Inside Sales"
                       If ws.Range("d29") = "" Then G_live = "Go Live Date on Phone"
                       If ws.Range("d33") = "" Then Candidate = "Candidate Type"
                       If ws.Range("d35") = "" Then Cube = "Cube Location"
                        If Len(S_date) > 0 Or Len(G_live) > 0 Or Len(Candidate) > 0 Or Len(Cube) > 0 Then msg = True
                   
                    Case "New Hire with Territory (RIC)"
                       If ws.Range("d27") = "" Then S_date = "Start Date with Inside Sales"
                       If ws.Range("d29") = "" Then G_live = "Go Live Date on Phone"
                       If ws.Range("d33") = "" Then Candidate = "Candidate Type"
                       If ws.Range("d39") = "" Then Division = "Inside Sales Division"
                        If Len(S_date) > 0 Or Len(G_live) > 0 Or Len(Candidate) > 0 Or Len(Division) > 0 Then msg = True
                    
                    Case "Role Change with Territory (RIC)"
                       If ws.Range("d35") = "" Then Cube = "Cube Location"
                       If ws.Range("d39") = "" Then Division = "Inside Sales Division"
                       If Len(Cube) > 0 Or Len(Division) > 0 Then msg = True
                    
                    Case "Termination"
                       If ws.Range("i41") = "" Then Term_type = "Termination Type"
                       If ws.Range("i43") = "" Then Term_date = "Termination Date"
                       If Len(Term_type) > 0 Or Len(Term_date) > 0 Then msg = True
                    
                    Case "Leave Of Absense"
                       If ws.Range("i34") = "" Then LOA_date = "LOA Effective Date"
                       If ws.Range("i36") = "" Then Return_date = "Date of Expected Return"
                       If Len(LOA_date) > 0 Or Len(Return_date) > 0 Then msg = True
                       
                    Case "Other"
                       If ws.Range("c51") = "" Then Notes = "Manager Notes"
                       If Len(Notes) > 0 Then msg = True
                       
               End Select
            
            
            If msg = True Then
               'select sheet so user can view when msgbox pops
               ws.Select
               'msgbox list each missing piece of information
               'ie "Missing Required Information:
               '   Start Date with inside sales
               '   Candidate Type
               '   Continue and save or cancel?
               
               mbResult = MsgBox(ws.Name & "   " & "Missing Information:" & vbCrLf & vbCrLf & _
                   IIf(Len(S_date), S_date & vbCrLf, "") & _
                   IIf(Len(G_live), G_live & vbCrLf, "") & _
                   IIf(Len(Candidate), Candidate & vbCrLf, "") & _
                   IIf(Len(Cube), Cube & vbCrLf, "") & _
                   IIf(Len(Division), Division & vbCrLf, "") & _
                   IIf(Len(Term_type), Term_type & vbCrLf, "") & _
                   IIf(Len(Term_date), Term_date & vbCrLf, "") & _
                   IIf(Len(LOA_date), LOA_date & vbCrLf, "") & _
                   IIf(Len(Return_date), Return_date & vbCrLf, "") & _
                   IIf(Len(Notes), Notes & vbCrLf, "") & _
                   vbCrLf & vbCrLf & "Click Cancel to stop and correct or" & _
                   vbCrLf & "OK to continue & save incomplete", vbOKCancel, "Missing information")
              ' set variables back to "" before next sheet
                   S_date = ""
                   G_live = ""
                   Candidate = ""
                   Cube = ""
                   Division = ""
                   New_title = ""
                   Role_change = ""
                   LOA_date = ""
                   Return_date = ""
                   Term_date = ""
                   Term_type = ""
                   Notes = ""
           
               If mbResult = vbCancel Then
        '           Cancel = True
                   Exit Sub
               End If
            End If
                msg = False
        End If
    End If
Next ws
End Sub