Ensure that no blanks are left in Date column

daithiboy

Board Regular
Joined
Jul 1, 2016
Messages
50
Hi Guys,

I have a couple of sheets that are accessed my one other person. I have dashboards with pivot tables that are all based around grouped dates. My colleague is in the habit of not filling the info out correctly and has been know to leave a date blank and refresh the workbook, hence removing all of my groupings which I then have to fix (dozens of pivot tables).

What I am wondering is, if I can force excel to only allow dates in certain columns and when a new row is added to the table that if the date field is not entered correctly then a hand comes out of the screen and serves up some excel justice across the face, or maybe just a dialog box, or I'd even be happy with "01/01/1900" in the cell. Anything to prevent my groupings from being lost.

I hope that is clear and any suggestions would be greatly appreciated.

Cheers,
Dave
 

Some videos you may like

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

kennypete

Board Regular
Joined
Apr 19, 2008
Messages
219
Office Version
365, 2019
Platform
Windows
Try this (where the highlighted cells are named "Compulsory"):

VBA Code:
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    intCount = ThisWorkbook.Names("Compulsory").RefersToRange.Count
    If WorksheetFunction.CountA(Worksheets("Sheet1").Range("Compulsory")) < intCount Then
        MsgBox "Workbook will not close unless ALL dates have been filled in!", , "Missing info"
        Cancel = True
    End If
End Sub
ensure-that-no-blanks-are-left-in-date-column.1119419.jpg

ensure-that-no-blanks-are-left-in-date-column.1119419(2).jpg


Now if any row or empty cell is in the Compulsory area the Workbook cannot be saved.
 

Watch MrExcel Video

Forum statistics

Threads
1,095,481
Messages
5,444,736
Members
405,298
Latest member
fxtrtr17

This Week's Hot Topics

Top