Message Error if Required Cells Have No Info

[RandyD123]

I have a worksheet called "LNC". This sheet will contain many many rows but only Columns A -G. What I want to do is if someone enters info on any any row AFTER A3 that cells A, B, C, D & G MUST have info in that row. Column E is hidden and will always have info based on date entered in column B, and F is the only column that can be left blank.

So lets say that we are on row 356 and someone enters info into cell B356 but no other cells except F, I want a msg box to say "You are missing info in cells A, B, C, D or G. Please Check and Save again".

So basically if you start to put info on any row, you must complete the row. Each row is added only when necessary so there could be hundreds of rows. Today I am on row 450 and everything is blank under that row. Just don't want people to start a row without finishing the row.

This could work with either before close or when trying to save, either way would be fine.

Any help would be greatly appreciated. Thank You!

 

[Jimmypop]

So you did delete the other code completely from Sheet LNC... Are you colors still going away?

Then the code will run if you try to close the workbook and not on save (Ctrl+S) for now... still seeing if we can suppress it asking to save and going directly back to the sheet...

 

[Jimmypop]

Ok last try....

1. Make sure the LNC Sheet code is deleted to check for missing info.(If you have other code here it should be fine, just make sure the code I gave is deleted.)
2. Paste the code below to the ThisWorkbook module... Essentially what it should do is not allow the user to save if there is any missing info in the range specified. This should work for Ctrl+S and selecting save via File Options. Also it should check if workbook is trying to be closed with missing info.
3. If all else fails maybe provide a link to the actual file (or a test version) via Drive or Dropbox so we can investigate further...

Private Sub Workbook_BeforeClose(Cancel As Boolean)
        Dim ws      As Worksheet
        Dim neValues As Range, neFormulas As Range, MyRange As Range
        Set MyRange = Union(Range("A:D"), Range("G:G")).Rows(ActiveCell.Row)
        Set ws = Worksheets("LNC")
        On Error Resume Next
        Set neValues = Intersect(ActiveCell.EntireRow.SpecialCells(xlConstants), MyRange)
        Set neFormulas = Intersect(ActiveCell.EntireRow.SpecialCells(xlFormulas), MyRange)
        On Error Resume Next
        If neValues Is Nothing And neFormulas Is Nothing Then
            MsgBox "You might be missing info in cells A, B, C, D or G. Please Check cells And Save again.", vbOKOnly
        ElseIf Not IsEmpty(neValues) And Not IsEmpty(neFormulas) Then
' Do Nothing
End If
End Sub
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
        Dim ws      As Worksheet
        Dim neValues As Range, neFormulas As Range, MyRange As Range
        Set MyRange = Union(Range("A:D"), Range("G:G")).Rows(ActiveCell.Row)
        Set ws = Worksheets("LNC")
        On Error Resume Next
        Set neValues = Intersect(ActiveCell.EntireRow.SpecialCells(xlConstants), MyRange)
        Set neFormulas = Intersect(ActiveCell.EntireRow.SpecialCells(xlFormulas), MyRange)
        On Error Resume Next
        If neValues Is Nothing And neFormulas Is Nothing Then
            MsgBox "You might be missing info in cells A, B, C, D or G. Please Check cells And Save again.", vbOKOnly
        ElseIf Not IsEmpty(neValues) And Not IsEmpty(neFormulas) Then
' Do Nothing
End If
End Sub