How to prevent saving if specific cell is blank in Excel?
For example, you designed a form in worksheet and share with your colleagues. You hope your colleagues fill their names into the specific cell to indicate who entered this form, otherwise prevent them from saving the form, how could you do? Here I will introduce a VBA macro to prevent saving a workbook if the specific cell is blank in Excel.
Recommended Productivity Tools for Excel
Office Tab: Bring powerful tabs to Office (include Excel), just like Chrome, Safari, Firefox and Internet Explorer. Save you half the time, and reduce thousands of mouse clicks for you. 30-day Unlimited Free Trial
Kutools for Excel: Save 71% of your time and solve 82% Excel problems for you. 300+ advanced tools designed for 1500+ work scenario, make Excel much easy and increase productivity immediately.60-day Unlimited Free Trial
Prevent saving if specific cell is blank in Excel
To prevent from saving current workbook if the specific cell is blank in Excel, you can apply the following VBA macro easily.
Step 1: Open the Microsoft Visual Basic for Applications window with pressing the Alt + F11 keys in a meantime.
Step 2: In the Project Explorer, expand the VBAProject (Your workbook name.xlsm) and Microsoft Excel Objects, and then double click the ThisWorkbook. See screen shot left:
Step 3: In the opening ThisWorkbook window, paste the following VBA macro:
VBA Macro: Prevent saving if specific cell is blank
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) If Application.Sheets("TEST").Range("A1").Value = "" Then Cancel = True MsgBox "Save cancelled" End If End SubNote: In the VBA code, the "TEST" is the specific worksheet name, and the "A1" is the specific cell, and you can change them as you need.
Now if the specific cell is blank in the current workbook, when you save it, a warning dialog box comes out and tells you "Save cancelled". See the following screen shot:
Recommended Productivity Tools
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
To post as a guest, your comment is unpublished.· 1 months agoHi i want to ask if it is from A2 to U2. what should i write?
To post as a guest, your comment is unpublished.· 1 years agohi - this was super helpful... Just had one question, how do I save the file without a value in that field? As I try to save, the VBA code will pop the "Save Cancelled" message which is the intended response, however, need to save once without a value to create the form to be reused.