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