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) 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:
You are guest
or post as a guest, but your post won't be published automatically.
- To post as a guest, your comment is unpublished.· 7 months agogood afternoon, I used the code above and it worked perfectly. my question is what should the code look like if I want to test on 2 cells? I am quite desperate. thanking you I advance for your assistance
- To post as a guest, your comment is unpublished.· 8 months agoI have a very big spreadsheet that contains a lot of info.
Can someone please help me with a code to copy into VBA - I want it to be that if Cell C2-C1000+ have any info in them then cell O2-O1000+ and P2-P1000+ requires user input - however if a cell in Column C is empty then the cell in Column O & P can be empty as well. (for example) if cell C3 doesn't have any data input then cell O3-P3 can be empty.
Thank you :)
- To post as a guest, your comment is unpublished.· 7 months agoHi Yzelle,
Please remember to place below code into “ThisWorkbook” script window, and rename the worksheet name “Test” in the below code based on your condition.
Dim xIRg As Range
Dim xSRg As Range
Dim xBol As Boolean
Dim xInt As Integer
Dim xStr As String
If ActiveSheet.Name = "Test" Then
Set xRg = Range("C:C")
Set xRRg = Intersect(xRg.Worksheet.UsedRange, xRg)
xBol = False
On Error Resume Next
For xInt = 1 To xRRg.Count
Set xIRg = xRRg.Item(xInt)
If xIRg.Value2 <> "" Then
Set xSRg = Nothing
If (Range("O" & xIRg.Row) = "") Or (Range("P" & xIRg.Row) = "") Then
xBol = True
If xBol Then
Cancel = True
MsgBox "Save cancelled"
- To post as a guest, your comment is unpublished.· 8 months agoThis is really great. Do you know what I can do to make this work for a range of sheets and a number of cells? Also, these cells cannot always be the same, as there are sheets generated in this specific workbook which may not have the same cell needing to be filled each time. The cells will always be in the same column, just above the page border which is also generated. Thanks!
- To post as a guest, your comment is unpublished.· 9 months agoHi, very useful. BUT there is a problem when I use it for files on the sharepoint. The changes are not saved but a new version is created that is displayed when reopening which is quite confusing. Is it possible to disable these new versions ?
- To post as a guest, your comment is unpublished.· 11 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.· 8 months agoHi Wkai,
Try this VBA code:
(This VBA code will detect Range A2:E5 in the Sheet “Test”, and cancel saving if there are blank cells existing in the range.)
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim xWSName As String
Dim xRgAddress As String
Dim xRg As Range
Dim xWs As Worksheet
Dim xFNRg As Range
xWSName = "TEST"
xRgAddress = "A2:E5"
Set xWs = Application.ActiveWorkbook.Worksheets.Item(xWSName)
Set xRg = xWs.Range(xRgAddress)
Set xFNRg = Nothing
On Error Resume Next
Set xFNRg = xRg.SpecialCells(xlCellTypeBlanks, 23)
If Not TypeName(xFNRg.count) = "Nothing" Then
Cancel = True
MsgBox "Save cancelled"
- To post as a guest, your comment is unpublished.· 2 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.
- To post as a guest, your comment is unpublished.· 2 years agoHi Eduardo,
What about typing a space in the specified cell to pretend to a blank cell? Please remind to remove the space in future!