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 September 18, 2017)

21

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

Changing the Axis Scale

When creating a chart, you may want to adjust the default scaling that Excel applies to an axis. This is relatively easy to ...

Discover More

Detecting Errors in Conditional Formatting Formulas

If an error exists in a formula tucked inside a conditional format, you may never know it is there. There are ways to find ...

Discover More

Displaying Administrative Tools on the Start Screen

Windows provides a number of administrative programs that can be very helpful when managing your system. To display these ...

Discover More

Comprehensive VBA Guide Visual Basic for Applications (VBA) is the language used for writing macros in all Office programs. This complete guide shows both professionals and novices how to master VBA in order to customize the entire Office suite for their needs. Check out Mastering VBA for Office 2010 today!

More ExcelTips (ribbon)

Conditionally Deleting Rows

Want to delete a bunch of rows in a worksheet based on the value in a certain cell of each row? There are a couple of ways ...

Discover More

Pulling Apart Cells

Separating text values in one cell into a group of other cells is a common need when dealing with text. Excel provides a ...

Discover More

Deleting All Names but a Few

Want to get rid of most of the names defined in your workbook? You can either delete them one by one or use the handy macro ...

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 8 - 2?

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

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.