Ensure that no blanks are left in Date column

[daithiboy]

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

 

[kennypete]

Try this (where the highlighted cells are named "Compulsory"):

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

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