Please Note: This article is written for users of the following Microsoft Excel versions: 2007, 2010, 2013, 2016, 2019, and Excel in Office 365. If you are using an earlier version (Excel 2003 or earlier), this tip may not work for you. For a version of this tip written specifically for earlier versions of Excel, click here: Requiring Input.

Requiring Input

by Allen Wyatt
(last updated December 3, 2019)


When you are developing a worksheet that will be used by other people, you may want to make sure that they fill in certain cells before they are allowed to close the workbook. There is no built-in function in Excel to do this, but you can create a macro that will make the necessary check and stop the user for proceeding. This can be a rather simple macro, tied to the BeforeClose event.

The BeforeClose event is triggered whenever a workbook is closed by whatever means. The trick is the setting of the Cancel property within the event handler. Setting Cancel to True will stop the closing of the workbook and leaving it unchanged results in the workbook closing normally.

For example, the following macro checks whether cell A1 has anything in it; if it does, then the workbook is closed. If it doesn't, then the user is informed that something is missing and the closing is canceled.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    If Cells(1, 1).Value = "" Then
        MsgBox "Please fill cell A1"
        Cancel = True
    End If
End Sub

More elaborate macros can be created, if desired. For instance, you might have several different cells that need to be checked. The following version checks a range named "Mandatory" to see if each cell in the range contains something. If any of the cells are empty, then the workbook cannot be saved or closed. (This macro is triggered not only during the BeforeClose event, but also during the BeforeSave event.) The two event handlers are put into the code for the workbook and the ForceDataEntry macro is placed in a regular macro module.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Cancel = ForceDataEntry()
End Sub
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _
  Cancel As Boolean)
    Cancel = ForceDataEntry()
End Sub
Sub ForceDataEntry() As Boolean
    Dim rng As Range
    Dim c As Variant
    Dim rngCount As Integer
    Dim CellCount As Integer

    Set rng = Range("Mandatory")
    rngCount = rng.Count

    CellCount = 0
    For Each c In rng
        If Len(c) > 0 Then
            CellCount = CellCount + 1
        End If
    Next c
    ForceDataEntry = False
    If CellCount <> rngCount Then
        ForceDataEntry = True
    End If
End Sub

You should note that any implementation that requires macros (like this one does) suffers from one potential problem—users can decide to not enable macros when the workbook is loaded. If they run the workbook with the macros disabled, then they will still be able to save the workbook without all the mandatory cells containing values.


If you would like to know how to use the macros described on this page (or on any other page on the ExcelTips sites), I've prepared a special page that includes helpful information. Click here to open that special page in a new browser tab.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (9574) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, and Excel in Office 365. You can find a version of this tip for the older menu interface of Excel here: Requiring Input.

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. ...


Drawing a Table

There are several ways you can create tables in a document, but one of the most unique (and perhaps most fun) is to ...

Discover More

Error when Double-Clicking Workbook Files

When you double-click an Excel workbook on your system, Windows has to do a lot of behind-the-scenes work to start Excel ...

Discover More

Conditional Calculations in Word

Word allows you to insert simple formulas, using fields, in table cells. You can also create simple conditional ...

Discover More

Program Successfully in Excel! John Walkenbach's name is synonymous with excellence in deciphering complex technical topics. With this comprehensive guide, "Mr. Spreadsheet" shows how to maximize your Excel experience using professional spreadsheet application development tips from his own personal bookshelf. Check out Excel 2013 Power Programming with VBA today!

More ExcelTips (ribbon)

Symbols Convert to Numbers in Excel

Insert a symbol into a cell, and it should stay there, right? What if the symbol changes to another character, such as a ...

Discover More

Quickly Deleting Rows and Columns

Deleting rows or columns is easy when you use the shortcut described in this tip. Just select the rows or columns and ...

Discover More

Automatically Breaking Text

Want to convert the text in a cell so that it wraps after every word? You could edit the cell and press Alt+Enter after ...

Discover More

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

View most recent newsletter.


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 2 + 1?

2019-01-15 04:02:42

Col Delane


In a nutshell, the answer to your question is No - some modification to code and how it is deployed is required.

The complexity of the adaptations to the options offered in order to provide you a solution depends on:
1. which macro option you're considering (i.e. the first single macro or the set of three)
2. what you're testing on each item sheet and how you can return a single result of those tests that represents whether the sheet is "complete" or "incomplete".

2019-01-14 11:04:04


I have a questions regarding this macro.

If the first tab/sheet is labeled as "blank template" and has this macro assigned to it, will the macro follow if the blank template is copied and then the sheet name is changed?

Our staff is never allowed to change the blank template. They are supposed to copy the template and rename it to the item they want to sell, but we still want them to complete specific cells (always the same as the template) on every sheet.

2019-01-02 12:56:30



I would like to mandate a comment in column D, if user selects a value of 1 (out of a drop down list of 1,2,3) in column C. For example, if the user selects "1" in column C5, then the user must not be able to move to next without filling in a comment in D5. Please help.


2018-09-27 16:42:41


I have a sheet for filling daily tasks related to my work. Most of the fields need to be entered or selected from the drop down. But sometimes users leave the fields blank. What I want is if one cell in a row has a value, then the user has to mandatorily enter values in the other specific cells and user should not be allowed to proceed without entering the values in That row.

2017-09-18 10:33:43

Dave Bonin

I develop many workbooks which have tools for my use but which others
should never have access to. I created a function which allows me to do
those things others cannot.

In this case, I would use the function to allow me to bypass the rules
requiring input.

My username in Microsoft Office is set to "Dave Bonin". You can check or
set yours from Excel using File > Options > General. Your User Name is
is the last field on the panel, at least for Office 2010.

This is the macro. It's a function that returns a TRUE or FALSE.

Function IsDeveloper(Optional ByVal BeChatty As Boolean = True) As Boolean
If InStr(UCase(Application.UserName), UCase("Bonin")) Then
IsDeveloper = True
IsDeveloper = False
If BeChatty = True Then
MsgBox "Only Dave Bonin is allowed to use" & vbCr & _
"this feature. You are not him.", vbOKOnly + vbCritical, _
"Not Authorized"
End If
End If
End Function

The optional BeChatty parameter determines whether I provide
the user with a message or not. Sometimes I want to tell the user
they are denied. Other times I don't. It depends on my context.

2017-09-18 03:32:08

Col Delane

1. Where you have a number of mandatory inputs, I would recommend adding a separate "ErrorCheck" worksheet to your workbook, and including thereon a table which identifies/describes each mandatory input and then has a formula (using whatever is appropriate) to return the result of your test as to whether or not the target input cell is populated. Then have a single result that assesses all these input cell tests >> if all return "Ok" or "TRUE" (whichever you return at the cell level test) then the Master Check can return Ok/TRUE or Error/FALSE as appropriate. It is the latter master result that your macro then checks, rather than have the macro do all the lower level checks behind the scenes and only on saving or closing. I would also make this "ErrorCheck" worksheet and the table clearly visible to the user so that they can determine before even trying to close the workbook that their work is done or not.

2. For those developers (being the "owner" or Administrator of the workbook) who regularly design, construct, test and/or maintain workbooks that utilise Event macros (like those described in this tip), I find the following two macro tools very useful to quickly toggle the event macro handler on/off whilst maintaining the workbook. (This overcomes the problem/s identified by Carl, Barry & Carol who registered the very first comments.) I store these macros in my Personal.xlsb macro workbook, and link each to a button on my QAT for easy access.

Sub DisableEvents()

' Needs to be run manually

Application.EnableEvents = False
MsgBox "VBA Event Handler is now disabled!" & vbLf & vbLf & "Don't forget to re-activate it!"
Application.StatusBar = "VBA Event Handler is disabled >> Don't forget to re-activate it!"

End Sub

Sub ResetEnableEvents()

' Resets Application.EnableEvents status to True when an event macro is interrupted.

' Needs to be run manually

Application.EnableEvents = True
MsgBox "VBA Event Handler now reset to True (ON)"
Application.StatusBar = False

End Sub

2017-02-23 10:39:30


Manon: You need a space before the underscore on the first line, just like what is shown in the tip.


2017-02-23 10:35:16


I get the following error when running the macro below: "Compile error: Expected function or variable".
The whole first line "Private Sub...Boolean)" is highlighted in yellow when the error message prompts.

Any way to solve this problem? Thanks in advance for your help!

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _
Cancel As Boolean)
Cancel = ForceDataEntry()
End Sub

2016-12-29 12:25:55


Thanks for the beautifull explanations. Really helped me a lot

2016-06-18 08:07:18

Willy Vanhaelen


Replace: Cancel = ForceDataEntry()
with: Cancel = ForceDataEntry

Again bad macro code in this tip!

2016-06-17 14:26:30


Hi, Not very knowledgeable when it comes to vba but you script is exactly what I was looking for. I have a problem however when I run the above macro I get a compile error: sub or function not defined. ForceDataEntry is highlighted in the Cancel = ForceDataEntry() line.
Thank you

2015-12-28 08:40:43


I have tried the first sub-routine and it worked just fine in a PO template worksheet I am devising for my sales staff.
Now, I need to know how to save this template with the mandatory fields empty before I distribute it to the sales staff for use as a blank template.

2015-11-12 19:39:40


Hoping Glenn Case can help with this one - wanting to use your 'skipit' method to save my template. This is my code - where would I put your code?

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

If Application.CountA(Range("C5,F6,L6,F7,I7,L7,SkipIt")) <> 3 Then

If Range("C5").Value = "" Then
MsgBox "Please enter a title for this SCR", vbExclamation, "Required Entry"
ElseIf Range("F6").Value = "" Then
MsgBox "Please enter an originator for this SCR", vbExclamation, "Required Entry"
ElseIf Range("L6").Value = "" Then
MsgBox "Please enter the Campaign Name", vbExclamation, "Required Entry"
ElseIf Range("F7").Value = "" Then
MsgBox "Please enter the FLocs for this SCR", vbExclamation, "Required Entry"
ElseIf Range("I7").Value = "" Then
MsgBox "Please enter the relevant Noti or WO numbers", vbExclamation, "Required Entry"
ElseIf Range("L7").Value = "" Then
MsgBox "Please enter the Campaign Event Code for this SCR", vbExclamation, "Required Entry"
End If
Cancel = True
End If
End Sub
Thanks in advance

2015-03-26 06:48:40



What code would I use for a userform with combo boxes, text boxes and two frames with option buttons...With over a hundred fields for one entry, I really need this but just can't seem to nail it.

2015-03-24 03:27:26


Is there anyway to do with this without the use of macros? Thank you!

2015-01-05 08:24:16

Kaushal Kapoor

This method works for the whole workbook as a result of which it will affect all the worksheets contained in the workbook as well. Is there a method where it effects only a specific worksheet and not the whole workbook? Please help me out with this. Thanks!

2014-12-19 06:01:10



Make sure the macro is located in the "ThisWorkBook" module.

Also ensure the macro security settings are set to allow macros to run, the highest security settings will allow a workbook to open but with macros disabled and no notification. I have in the past hidden the actual/working worksheets in the WorkBook_Close and WorkBook_Save macros and substituted a worksheet with a message telling the User to re-open the workbook with macros enabled (the WorkBook_Open macro of course hides this warning message and unhides the actual/working worksheets).

2014-12-18 16:30:20


Any reason why the first example doesn't work? I've tried it in an existing workbook, and in a new one, and each time I exit them without filling in the cell, no pause, just an exit. Help! :)

2014-09-26 12:55:10


Thank you very much for your prompt response, Willy.

It still does not work, Excel simply ignores the macro and allows me to save/close workbooks without the required cells being populated.

I even tried the simpler macro (the first one in this article that checks whether A1 is populated), and it still did nothing.

Could it be something wrong with my Excel settings (Options -> Macro or Trust settings) ?

I am saving these as xlsm.

Thank you very much in advance!!

2014-09-26 05:36:19

Willy Vanhaelen


Sub ForceDataEntry() As Boolean


Sub ForceDataEntry()

2014-09-25 21:57:38


I tried using the macro shown by Allen but "Sub ForceDataEntry() As Boolean" is highlighted red and the error message is "Compile error: Expected: end of statement".

Has anyone else seen this issue? I appreciate the help, thank you!

2014-08-27 10:48:35

Glenn Case


Use Barry's suggestion to do what you want. Create a named cell somewhere away from the main portion of the spreadsheet, say A200, called SkipIt. Then insert the following lines at the beginning of the ForceDataEntry macro:

If Range("Skipit").Value = "TRUE" Then
Range("Skipit").Value = ""
Exit Sub
End If

Now to save or exit your spreadsheet, enter "TRUE" into A200.

2014-08-26 12:52:05


I have the same problem. I got to have the cells mandatory for input, but I want to be able to leave the form blank when I save it as the creator. Please help if someone knows how to do this!!!

2014-05-08 15:37:33


I've had a similar issue myself in the past. You can set a break-point within the "beforeclose" and "beforesave" macros and then skip over the code which sets the "Cancel" flag. Or have a special password entered in a cell which is tested, when the workbook is being closed/saved, which if correct then deletes the password and bypasses the test on the 'required' cells, leaving the "Cancel" flag unset.

You may want to apply some validation to the 'required' inputs as Users will put any old rubbish in just so that they can exit e.g. a period or just a space character.

2014-05-07 19:55:43


This works! My problem is that I need other people to enter data not me. How can I save it without excel requiring me to enter data?

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

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.