How to Prevent Blank Cells in a Given Range?

[nickolushenry]

Creating a log sheet multiple reps will log data into. I want to prevent reps from leaving cells blank. I would like a VB/validation code that I could copy into the worksheet so that if a cell in column B should becomes active with data, all columns to the right of that cell must contain data before the sheet can be closed and saved. <?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
Example:<o></o>
<o></o>
Rep enters data into B2. Before such rep can close the sheet or save it, they must enter all the required data into the remaining cells on Row2 B2:X2<o></o>
<o></o>
I would like a pop up to appear if at all possible should they try to save or close it with a blank cell. <o></o>
<o></o>
Currently this is the VBA code I have associated to my worksheet. It just generates the date and time in a given column. <o></o>
<o></o>
Dim bDoNotChange As Boolean<o></o>
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect([C2:C6550], Target) Is Nothing Then
Application.EnableEvents = False
Cells(Target.Row, 15) = Now
Application.EnableEvents = True
End If
End Sub<o></o>

Thanks for any and all help given!!

 

[JoeMo]

Try these in the Thisworkbook module:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
ThisWorkbook.Save
If Not ThisWorkbook.Saved Then Cancel = True
End Sub

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim sht As Worksheet, rng As Range, lRw As Long, needEntries As String
Set sht = ThisWorkbook.Sheets("Sheet5") 'Adjust sheet name to suit
lRw = sht.Range("B" & Rows.Count).End(xlUp).Row
Set rng = sht.Range("B2:X" & lRw)
For Each rw In rng.Rows
    If Not IsEmpty(rw.Cells(1, 1)) Then
        If WorksheetFunction.CountA(rw) < rw.Columns.Count Then
            needEntries = needEntries & "," & rw.Address
        End If
    End If
Next rw
If needEntries = vbNullString Then
    Application.DisplayAlerts = False
    Exit Sub
Else
    msg = "The following rows must be filled with entries before this workbook can be saved or closed:" & vbCrLf & vbCrLf
    msg = msg & Right(needEntries, Len(needEntries) - 1)
    MsgBox msg
    Cancel = True
End If        
End Sub

Note: In the Before Save module, adjust the Sheet name to match the name of the sheet where entries are made. Above assumes this is "Sheet5".

 

[nickolushenry]

JoeMo THANK YOU SOO MUCH!!!! WORKS PERFECT!!