Help on making sure all the data is filled in

[Zimmerman]

Can someone help me with this. I have a spreadsheet that our sales department fills out. They enter a lot of different data in cells all around the sheet which is important information that our production staff and our shipping staff needs, and sometime they fail to enter all of it which leaves questions. I would like to have the screen flash what cells need to be filled in before they save the sheet. Is it possible?

Thanks in advance
Jamie

 

[VoG]

Try this

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim MyRng As String, MissingList As String, c As Range
With Sheets("Master")
    If .Range("AA18").Value = "Yes" Then
        MyRng = "AA18, C2, J2, M2, D51, K51"
    Else
        MyRng = "AA18, C2, J3"
    End If
        If WorksheetFunction.CountA(.Range(MyRng)) < .Range(MyRng).Count Then
            Cancel = True
            For Each c In .Range(MyRng)
                If c.Value = "" Then MissingList = MissingList & c.Address(False, False) & ", "
            Next c
            MsgBox "You must complete " & Left(MissingList, Len(MissingList) - 2) & vbCrLf & "Save cancelled!", vbExclamation
        End If
End With
End Sub

 

[Zimmerman]

Absolute genius!! Thanks so much!!

Where did you or how did you learn so much about this?

Thanks again for everything!!

Jamie Zimmerman

 

[VoG]

You are welcome.

Where did you or how did you learn so much about this?

I've learned a lot simply by lurking on this board plus trying to solve 'impossible' questions like the last one you asked

 

[Zimmerman]

Is there a way to make the cells that are not filled in yet blink?

 

[VoG]

Making cells blink is not straightforward and most users find it intensely irritating after a while.

This will colour the missing cells red.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim MyRng As String, MissingList As String, c As Range
With Sheets("Master")
    If .Range("AA18").Value = "Yes" Then
        MyRng = "AA18, C2, J2, M2, D51, K51"
    Else
        MyRng = "AA18, C2, J3"
    End If
        .Range(MyRng).Interior.ColorIndex = xlNone
        If WorksheetFunction.CountA(.Range(MyRng)) < .Range(MyRng).Count Then
            Cancel = True
            For Each c In .Range(MyRng)
                If c.Value = "" Then
                    MissingList = MissingList & c.Address(False, False) & ", "
                    c.Interior.ColorIndex = 3
                End If
            Next c
            MsgBox "You must complete " & Left(MissingList, Len(MissingList) - 2) & vbCrLf & "Save cancelled!", vbExclamation
        End If
End With
End Sub