Mandatory Cell Input

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.

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

4. If the above option is not available then; select  view–project 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 belo

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

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

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.

Further Reading:

1. Mandatory Cell Input
2. Custom Excel Function
3. Using Goal Seek
4. DATEDIF Function