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.
Prevent saving if specific cell is blank in Excel
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 Sub1. 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:
Related Articles
How to check or find if cell contains specific string/text/word in Excel?
How to copy rows if column contains specific text/value in Excel?
How to copy cells if column contains specific value/text in Excel?
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
