Written by Allen Wyatt (last updated January 21, 2023)
This tip applies to Excel 2007, 2010, 2013, 2016, 2019, 2021, and Excel in Microsoft 365
Riley has a worksheet that he needs to work with every Friday. This worksheet is based on a template, and there are, at minimum, five cells he needs to fill in, in the range C4:C8. When he starts with the worksheet, these cells are blank. Riley wonders if there is a way to prevent the worksheet from being saved and/or closed until he fills in all five of these cells.
There is a way to do this, but it involves the use of macros. Excel supports the concept of event handlers, which means that you can develop macros that run, automatically, when certain events occur. Two events for which you can create special event handlers are BeforeClose (meaning, before the workbook closes) and BeforeSave (before the workbook is saved).
As an example of how this could work, let's say that the worksheet containing the range to be checked (C4:C8) is named "MyData." You could add this code to the ThisWorkbook module:
Private Sub Workbook_BeforeClose(Cancel As Boolean) Dim rng As Range Dim iCount As Integer Dim sTemp As String Set rng = Worksheets("MyData").Range("C4:C8") iCount = Application.WorksheetFunction.CountBlank(rng) If iCount <> 0 Then sTemp = rng.Address & " has blank cells. " & vbCrLf sTemp = sTemp & "The workbook will not be closed." MsgBox sTemp Cancel = True End If End Sub
Note that the macro relies on the CountBlank worksheet function to determine if there are any blanks in the range of cells. If it detects any blank cekks (iCount is greater than 0), then the macro displays a message to the user and the Cancel variable is set to True, which actually stops the workbook from closing.
You can use a similar macro for the BeforeSave event, in this manner:
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) Dim rng As Range Dim iCount As Integer Dim sTemp As String Set rng = Worksheets("MyData").Range("C4:C8") iCount = Application.WorksheetFunction.CountBlank(rng) If iCount <> 0 Then sTemp = rng.Address & " has blank cells. " & vbCrLf sTemp = sTemp & "The workbook will not be saved." MsgBox sTemp Cancel = True End If End Sub
If you wanted to make sure that the routine selected the cells in which input is needed (as a final step), you could add the following line to both macros, right after the line that sets the Cancel variable to True:
rng.Select
Remember, as well, that since your workbook is based on a template, it will need to be saved as a macro-enabled template in order to work properly.
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (4364) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, 2021, and Excel in Microsoft 365.
Dive Deep into Macros! Make Excel do things you thought were impossible, discover techniques you won't find anywhere else, and create powerful automated reports. Bill Jelen and Tracy Syrstad help you instantly visualize information to make it actionable. You’ll find step-by-step instructions, real-world case studies, and 50 workbooks packed with examples and solutions. Check out Microsoft Excel 2019 VBA and Macros today!
Macros that run automatically when you open or close a workbook are quite helpful. You may not want them to run, however, ...
Discover MoreSometimes it may be helpful for a macro to know exactly where it is being executed. This tip provides a way that you can ...
Discover MoreWhen creating a workbook to be used by others, you may want any worksheets they add to the workbook to contain some ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
2023-01-21 10:57:21
Tomek
To improve on this approach, you can additionally specify data validation for cells in question. That way you can prevent the user from bypassing the requirement to fill the cells by putting just spaces there, as spaces are not counted as blanks. This way you can force the cells to accept only numbers, numbers that are limited in range, dates , times, or only entries that are specified in a list. You can also specify minimum and maximum for text length.
This way you can also limit incorrect information the user can inadvertently put there.
Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2025 Sharon Parq Associates, Inc.
Comments