How to prevent saving if specific cell is blank in Excel?

Imagine you've created a form in Excel and shared it with your colleagues. You want them to fill in their names in a specific cell to identify who entered the information. If the cell is left blank, you want to prevent the form from being saved. In this tutorial, I'll show you how to use a VBA macro to block the saving process if the designated cell is empty in Excel.

---

Prevent saving if specific cell is blank in Excel

To prevent saving the current workbook if the specific cell is blank in Excel, you can easily apply the following VBA macro.

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 screenshot:

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

Note:
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 screenshot:

---

Related Articles