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