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]

Hi There

Maybe something like: (works only after ENTER key is pressed in Column G - still need a workaround to automate a bit more but so far this is what I have)

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Column = 7 Then
        Dim ws      As Worksheet
        Dim ColorRng As Range
        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")
        Set ColorRng = MyRange
        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 Highlighted cells And Save again. Highlights will auto refresh after Next row entered."
            ColorRng.Offset(-1, 0).SpecialCells(xlCellTypeBlanks).Interior.Color = RGB(255, 0, 0)
        ElseIf Not IsEmpty(neValues) And Not IsEmpty(neFormulas) Then
            UsedRange.Interior.Color = RGB(255, 255, 255)
        End If
    End If
End Sub

 

[RandyD123]

Hi There

Maybe something like: (works only after ENTER key is pressed in Column G - still need a workaround to automate a bit more but so far this is what I have)

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Column = 7 Then
        Dim ws      As Worksheet
        Dim ColorRng As Range
        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")
        Set ColorRng = MyRange
        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 Highlighted cells And Save again. Highlights will auto refresh after Next row entered."
            ColorRng.Offset(-1, 0).SpecialCells(xlCellTypeBlanks).Interior.Color = RGB(255, 0, 0)
        ElseIf Not IsEmpty(neValues) And Not IsEmpty(neFormulas) Then
            UsedRange.Interior.Color = RGB(255, 255, 255)
        End If
    End If
End Sub

Didn't seem to work after pressing ENTER in G column. I put this code in the "ThisWorkbook". I think it would be better for it to work when the save function is used, that way there would be no need to hit enter.

 

[Jimmypop]

Didn't seem to work after pressing ENTER in G column. I put this code in the "ThisWorkbook". I think it would be better for it to work when the save function is used, that way there would be no need to hit enter.

Hi...

You would neet to add it to the sheet code seeing as it tracks on worksheet...

Just a moment for the save part...

 

[RandyD123]

I works more or less, but I see one issue. When I click the G column my colors go away in Rows 1 and 2 even before I hit enter. See attached pic.

 

[Jimmypop]

Apologies... That is before I knew you had custom colors... Working on a fix currently

 

[Jimmypop]

Hi Randy

Try the below...

Private Sub Workbook_BeforeClose(Cancel As Boolean)
        Dim ws      As Worksheet
        Dim ColorRng As Range
        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")
        Set ColorRng = MyRange
        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."
        ElseIf Not IsEmpty(neValues) And Not IsEmpty(neFormulas) Then
' Do Nothing
End If
End Sub

 

[RandyD123]

Hi Randy

Try the below...

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")
        Set ColorRng = MyRange
        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."
        Else
        ' Do Nothing
        End If
End Sub

Colors still go away in Rows 1 and 2 as soon as I click in the G column. And now I don't get any error msg. And it will allow me to save without any error msg!

 

[Jimmypop]

Colors still go away in Rows 1 and 2 as soon as I click in the G column. And now I don't get any error msg. And it will allow me to save without any error msg!

Ok did you delete the other code from the sheet event...then add this one to the workbook...

So basically before the Workbook is closed the code runs and user can then cancel the save and go and correct the missing cells...

 

[RandyD123]

Ok did you delete the other code from the sheet event...then add this one to the workbook...

So basically before the Workbook is closed the code runs and user can then cancel the save and go and correct the missing cells...

I put the code in post #8 in the "ThisWorkbook" and it doesn't see to work. Then I put it in the Sheet1 (LNC) and it didn't work......