Please Note: This article is written for users of the following Microsoft Excel versions: 2007, 2010, and 2013. 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 March 8, 2014)

19

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

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (9574) applies to Microsoft Excel 2007, 2010, and 2013. 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. ...

MORE FROM ALLEN

Expiration Date for Excel Programs

If you use Excel to create a macro-based application, you may want to make sure that your programs cease working after a ...

Discover More

Problems Pasting Large Pictures

If you insert a large picture in your document and your text jumps all around and the picture seems to disappear, don't ...

Discover More

Viewing Files of a Certain Type

When you choose to open a file, Word normally displays only those files that end with the .DOCX or .DOCM extensions. If you ...

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)

Limiting Input to a Format

When setting up a worksheet for others to use, you might want to make some limitations on what can be entered in certain ...

Discover More

Can't Copy Data between Workbooks

Edit a group of workbooks at the same time and you probably will find yourself trying to copy information from one of those ...

Discover More

Canceling an Edit

When editing a cell, you may want to cancel the edit at some point. There are two ways to do this, both described in this ...

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 8Mpixels. 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 0 + 7?

2017-02-23 10:39:30

Allen

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

-Allen


2017-02-23 10:35:16

Manon

Hi,
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

Wannes

Thanks for the beautifull explanations. Really helped me a lot


2016-06-18 08:07:18

Willy Vanhaelen

@Brian

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

Again bad macro code in this tip!


2016-06-17 14:26:30

Brian

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

Aref

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

Leanne

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"
Range("C5").Select
ElseIf Range("F6").Value = "" Then
MsgBox "Please enter an originator for this SCR", vbExclamation, "Required Entry"
Range("F6").Select
ElseIf Range("L6").Value = "" Then
MsgBox "Please enter the Campaign Name", vbExclamation, "Required Entry"
Range("L6").Select
ElseIf Range("F7").Value = "" Then
MsgBox "Please enter the FLocs for this SCR", vbExclamation, "Required Entry"
Range("F7").Select
ElseIf Range("I7").Value = "" Then
MsgBox "Please enter the relevant Noti or WO numbers", vbExclamation, "Required Entry"
Range("I7").Select
ElseIf Range("L7").Value = "" Then
MsgBox "Please enter the Campaign Event Code for this SCR", vbExclamation, "Required Entry"
Range("L7").Select
End If
Cancel = True
Else
End If
End Sub
Thanks in advance


2015-03-26 06:48:40

Ansie

Hi,

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

Andy

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

Barry

@Dave/Bruno

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

Dave

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

Bruno

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

Replace

Sub ForceDataEntry() As Boolean

with

Sub ForceDataEntry()


2014-09-25 21:57:38

Bruno

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

Carl:

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

Carl

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

Barry

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

Carol

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