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.
- Reuse Anything: Add the most used or complex formulas, charts and anything else to your favorites, and quickly reuse them in the future.
- More than 20 text features: Extract Number from Text String; Extract or Remove Part of Texts; Convert Numbers and Currencies to English Words.
- Merge Tools: Multiple Workbooks and Sheets into One; Merge Multiple Cells/Rows/Columns Without Losing Data; Merge Duplicate Rows and Sum.
- Split Tools: Split Data into Multiple Sheets Based on Value; One Workbook to Multiple Excel, PDF or CSV Files; One Column to Multiple Columns.
- Paste Skipping Hidden/Filtered Rows; Count And Sum by Background Color; Send Personalized Emails to Multiple Recipients in Bulk.
- Super Filter: Create advanced filter schemes and apply to any sheets; Sort by week, day, frequency and more; Filter by bold, formulas, comment...
- More than 300 powerful features; Works with Office 2007-2019 and 365; Supports all languages; Easy deploying in your enterprise or organization.
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) 'Update by ExtendOffice 20220907 Dim xFileName As String Dim xStr As String Dim xStrWSH As String Dim xWSh As Worksheet Dim xWShs As Sheets Dim xWSh1 As Worksheet Dim xWB As Workbook xStrWSH = "xHidWSH_LJY" On Error Resume Next Set xWB = Application.ActiveWorkbook Set xWShs = xWB.Worksheets Set xWSh = xWShs.Item(xStrWSH) If xWSh Is Nothing Then Set xWSh1 = xWShs.Add xWSh1.Name = xStrWSH xWSh1.Visible = xlSheetVeryHidden Cancel = False Else If Trim(Application.Sheets("Sheet1").Range("A1").Value) = "" Then Cancel = True MsgBox "Save cancelled" End If End If End Sub
1. In the 26th row of the above VBA code, the "Sheet1" is the specific worksheet name, and the "A1" is the specific cell, and you can change them as you need.
2. After you enter the VBA in ThisWorkbook, you should save the workbook first. And then you can send the macro-enabled file to others.
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: