Making Sure Cells are Filled In before Saving

by Allen Wyatt
(last updated October 7, 2017)

2

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 neeed (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, and 2016.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

Entering the Current Time

Need to enter the current time into a cell? It's easy to do using this keyboard shortcut. The shortcut is a handy one to ...

Discover More

Identifying the Last Cell Changed in a Worksheet

When someone changes a cell in a worksheet, Excel normally goes along its merry way of keeping everything up to date. It ...

Discover More

Aligning Text on a Specific Character

Want to use tab stops to align text according to the position of a certain character? It's easy to do if that character ...

Discover More

Save Time and Supercharge Excel! Automate virtually any routine task and save yourself hours, days, maybe even weeks. Then, learn how to make Excel do things you thought were simply impossible! Mastering advanced Excel macros has never been easier. Check out Excel 2010 VBA and Macros today!

More ExcelTips (ribbon)

Delimited Text-to-Columns in a Macro

The Text-to-Columns tool is an extremely powerful feature that allows you to divide data in a variety of ways. Excel even ...

Discover More

Determining the Length of a String

Macros are great for working with strings, and one of the most commonly used string functions is Len. This tip explains ...

Discover More

Macros Run Fine Individually, but Not Collectively

Developing macros can be rewarding, but it can also be challenging. Getting individual macros to run properly is hard ...

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 6Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is 9 + 9?

2017-10-09 07:26:58

Nick from London

I saw the query some time after it was posed.

if you are going to use a macro, you could consider a User Form that opens when the Template opens and asks for the items of information, possibly with a user friendly description. The form could be designed only to close once the data had been input.


2017-10-09 04:06:44

Lionel ACHARD

rng.Select doesn't select the empty cells, you could rather use rng.SpecialCells(xlCellTypeBlanks).Select.
If the form is long, the missing values could be hiden on the bottom of the page, or event on an other sheet.


This Site

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.

Newest Tips
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.