Business Automation and Software Blog

Excel Tips and Tricks: Mandatory Cell Input

Posted by Robert Baran on Fri, Aug 10, 2012 @ 10:40 AM

Excel Tips and Tricks / Business Reporting Question: How do I make a specific cell mandatory to fill in, in an Excel workbook? We have a form that employees fill in but require that important information like employee names to be mandatory. Can this be done in Excel?

Answer: Yes, by using Visual Basic For Applications (VBA)

Why: To make a specific cell mandatory

Applies to MS Excel 2003, 2007, 2010: (Hint: Your Macro Security settings need to be enabled)

Example: Make cell B3 mandatory for completion in a workbook.

Excel Tips and Tricks

Refer to the steps given below:

1. Open Excel.

2. Press Alt + F11 to open VBA for Excel.

3. Double click on this workbook on the drop down list.

Excel Tips and Tricks

4. If the above option is not available then; select viewproject explorer, and you’ll be able to proceed with the below steps.

5. On your top right hand side select the first drop down arrow and choose workbook as given below.

6. Select the second drop down arrow and choose BeforeSave as displayed below.

Excel Tips and Tricks

7. Enter the following code:

If Cells(3, 2).Value = "" Then

MsgBox "Cell B3 requires user input"

Cancel = True

End If

8. Save the Macro.

9. To return to Excel press ALT + Q .

10. Save the workbook (For Excel 2007 and Excel 2010 save workbook as a .xlsm).

11. When you save the workbook it will prompt you to fill in B3 before saving.

Excel Tips and Tricks

This tip will only work when one tries to save. Meaning the workbook won’t be saved as long as cell B3 is empty. By so doing cell B3 is now a mandatory cell. Important information such as employee names will thus be entered resulting in forms being completed. You may have to check your macro security settings should the tip not work.

Contact PositiveVision ► ► for a Complimentary  Reporting Consultation

Topics: Business Reporting, Excel Tips and Tricks