Prevent user from closing worksheet if cell is blank

Okay, I think this does everything you specified, except for shifting the columns based on user. If you can get this much of it working, we can look at the multi-user part after.

1. Open up the VBA Editor (Alt + F11)
2. Under the VBA Project for your workbook, double-click on "ThisWorkbook"
3. Paste all the code below (this is assuming you don't already have any code in this area)

Please Login or Register  to view this content.

Note, you will not be able to save the workbook if there is anything already below line 1000. If that is a problem, change all the places in the code where I specify "1000" to be your actual last row of data.

I could not get the code from gyclone to work and tried another code (below) and could not get that to work either. Basically, I need my range in the worksheet to begin at row 5494 and continue as new rows are created. I'd like the range to be set that if something is entered in column D of the row, Column A and C need to be completed (Column B is hidden, no entry required). The first time I try the code, it works fine and gives me the message prompt or saves the file, but when I reopen it and try again I get an error. Hoping someone can help. Below is the code I was trying to use. The "de-bug" option highlights "rMyRng.SpecialCells(xlCellTypeBlanks).Select" but I'm not sure that is the only issue.


Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim nRws As Long, nFilCnt As Long, rMyRng As Range

With Sheets("DOMESTIC")
nRws = .Cells(Rows.Count, "D").End(xlUp).Row
Set rMyRng = Range("a5494:d" & nRws)
nFilCnt = WorksheetFunction.CountA(rMyRng)
If (nRws - 1) * 3 <> nFilCnt Then
rMyRng.SpecialCells(xlCellTypeBlanks).Select
MsgBox "Fill The Selected Blank Cells to Exit This File", vbCritical, "Action Required"
Cancel = True
End If
End With
End Sub

Hi Glycone,

I am a beginner in VBA so am not sure what I need to write.

My worksheet is currently populated with information in rows 1-5494. Many of these rows (97%) do not have any text entered into the date column (Column C).

Since the data spans over many years, it is not possible to go back and populate those cells.

However, going forward, I would like to enforce the discipline of entering the date (Column C) before the worksheet is closed, provided that the cell in Column D is populated with a part number once a new line/row is typed into beginning with row 5495.

Hoping you can provide insight.

Thanks,

Solus,
Thanks for this, it works perfectly.