Tom’s Tutorials For Excel: Preventing Save or Close Unless Cells are Filled
Sometimes you may want to prevent the workbook from being saved or closed unless data has been entered into certain cells, such as for on-sheet forms. The VBA code to accomplish this would go into your workbook module.
To easily access your workbook module, in Excel version 2003 or before, find the little Excel workbook icon near the upper left corner of your workbook window, usually just to the immediate left of the File menu option. Right-click on that icon, and left click on View Code. In Excel version 2007 or after, from your worksheet press Alt+F11, then press Ctrl+R, find your workbook name in the “Project – VBAProject” left vertical pane. Expand the Microsoft Excel Object folder for your workbook, right-click on ThisWorkbook and left-click to select View Code.
Paste the following procedure that prevents the workbook from being saved (with the example for six cells in range A1:A6 on Sheet1, so modify as needed) into the large white area that is the workbook module. Then press Alt+Q to return to the worksheet.
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
If WorksheetFunction.CountA( _
Worksheets("Sheet1").Range("A1,A2,A3,A5,A5, A6")) < 6 Then
MsgBox "Workbook will not be saved unless" & vbCrLf & _
"All required fields have been filled in!", , "Missing info"
Cancel = True
End If
End Sub
You can stick that same code into a Before_Close event, example:
Private Sub Workbook_BeforeClose(Cancel As Boolean)
If WorksheetFunction.CountA( _
Worksheets("Sheet1").Range("A1,A2,A3,A5,A5, A6")) < 6 Then
MsgBox "Workbook will not close unless" & vbCrLf & _
"All required fields have been filled in!", , "Missing info"
Cancel = True
Exit Sub
End If
Hi Tom
I have tried using the above code in my workbook and I am getting the warning messages but even if I fill in the required cells, if I try to save or exit, I am still getting the messages.
For test purposes I have used the exact code as above. Any Suggestions?
Please accept my apologies as I am very new to Excel and VBA.
Regards
Jason
Hello Jason, thank you for following my Excel bolg.
I know the code works, so here are a few ideas for you to look at. Are you sure…
• the worksheet you are testing this for is Sheet1.
• you are working with cells A1, A2, A3, A4, A5, and A6.
• each of those 6 cells all have some kind of data in them.
• none of those cells are merged with each other or merged with any other cells.
Feel free to post back if you are still stuck. It sounds like something minor that you are missing. And trust me, I can relate to how as a newcomer to VBA it is not easy to spot a nunace here and there. We were all beginners at one time, so no worries with asking any kind of question in order to get this to work for you. Post back so I can know how it turns out.
Hi Tom,
I have gone through all replies, tried to find solution to my problem analyzing those solutions given, unfortunately I couldn’t make it. Please support me.
I have a information chart and need to input figure in cell B2 to B20.
If user input value at least in any one cell from B2 to B20, user must fill at least any one cell F5 to F7, otherwise document will not be save and close.
Please suggest me the code, I will be great full to you.
Thanks.
Khairul
Hello Khairul:
Thanks for looking through all the comments first. You are right, the kind of question you are asking is unique and has not been discussed.
What is the name of the worksheet in your workbook where these ranges exist.
Hi Tom,
Thanks for the useful post about the vba macro, although I have another problem: the macro runs fine as when I try to save the document it displays the missing info message, but it won’t let me at any time to save my own file (as I’m the editor, I would want excel to let me save the file although the required cells are not filled in, but restrict others than me to do so).
Thanks in advance, rgds.
Brandon J
Hello Brandon, thanks for following my Excel blog. Try inserting a line of code at the beginning of those procedures that ends the procedure if you are you. To have VBA recognize that, find out what your username is. Open Excel and go into the VBE. Hit Ctrl+G. Type this in and then hit Enter:
? Application.Username
When I do that, I see Thomas Urtis
Let’s say what you see is Brandon Smith
So, insert this line of code as the first line:
If Application.UserName = “Brandon Smith” Then Exit Sub
Post back with how it turns out.
Hi Tom,
Would this still work say if my colleague did Save As?
Not sure I understand your question. If it works for you it should work for your colleagues.
Hello Tom,
Your post was exactly what I was looking for and it works like a charm….
The only issue I am having is that I as the owner of the file (but not the one who has to fill it in) am unable to save and distribute the file… it there a way I can save the file with the macros without getting the messages, the file has to go unfilled in order to get other users fill it… I hope I made myself clear….
Many thanks in advance!
Thanks for following my blog page.
Your question is a good one but I’ll be out for the day, and in fact most of this week. You can get a more comprehensive look at your question from many people who can help you at this popular forum, where I am also a contributor:
http://www.mrexcel.com/forum/excel-questions/
Just explain in a bit more detail over there what your code is doing and anything else you can think of that will help them understand your problem.
Hi Tom,
Thank you for explaning these items with so much details. I inserted the code in my workbook and it is working perfectly.
However, I have the same problem as Brandon: as I’m the editor, I would want excel to let me save the file although the required cells are not filled in, but restrict others than me to do so.
I inserted the line of code that you provided but it gives me an error:
If Application.UserName = “Van Ginkel, Mirjam Carina” Then Exit Sub
The error I get is: Compile Error: Invalid Outside Procedure.
Do you know what this can be?
Thanks a lot for your help.
Regards, Mirjam
It seems that the code line you inserted should work. The kind of error you received sounds like maybe you inserted that code line in the wrong place. Actually you should insert it into both procedures (BeforeSave and BeforeClose), and it should be immediately below the Sub line. If you are still stuck, post back and copy the code exactly as you have it with that line inserted, which might give a clue as to the reason for your error.
Thanks Tom.
I have figured it out and fixed the code. My next question is how do I adapt the code you provided above so that it takes into consideration when the sheet name is changed? In my code above it is named “RMPK Req”, but I have designed another code that updates the tab with the info typed in the description field. Also, a user may have multiple tabs of these so one file can have 3 or even 6 tabs of the same forms for different item and I would like to make sure that all mandatory fields are filled in otherwise they cannot save or close the file.
Hi there this works great but I have a large number of cells I want to restrict in this file of mine. I have 5 different ranges of cells. For example. F18:F30, F35:47, F52:F65 and so on. How can I do this without having to enter each individual field. Thanks
Hi there I just realised that I want the enforcement to enter a value in a field to be conditional on the adjacent having a value? Is this possible? Thanks
Hi There,
I wanted to use the coding provided above and getting mismatch type error. Could you please tell me what I have to change?
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
If Application.UserName = “Katherine Chung” Then Exit Sub
If Application.UserName = “Gurinder Dhillon” Then
If WorksheetFunction.CountA(Worksheets(“RMPK Req”).Range(“D13,D18,D19,G14,G18”)) < 5 Then MsgBox "Workbook will not be saved unless" & vbCrLf & "All required fields have been filled in!", "Missing info"
Cancel = True
ElseIf WorksheetFunction.CountA _
(Worksheets("RMPK Req").Range("D55,D56,D57,D58,D60,D64,G60,G61,G63,G65,G66")) < 11 Then MsgBox "Workbook will not be saved unless" & vbCrLf & "All required fields have been filled in!", "Missing info"
Cancel = True
End If
End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)
If Application.UserName = "Katherine Chung" Then Exit Sub
If Application.UserName = "Gurinder Dhillon" Then
If WorksheetFunction.CountA(Worksheets("RMPK Req").Range("D13,D18,D19,G14,G18")) < 5 Then MsgBox "Workbook will not be saved unless" & vbCrLf & "All required fields have been filled in!", "Missing info"
Cancel = True
ElseIf WorksheetFunction.CountA _
(Worksheets("RMPK Req").Range("D55,D56,D57,D58,D60,D64,G60,G61,G63,G65,G66")) < 11 Then MsgBox "Workbook will not be saved unless" & vbCrLf & "All required fields have been filled in!", "Missing info"
Cancel = True
End If
End Sub
Where exactly is your erroring code line, and why do you exclude Katherine Chung but allow Gurinder Dhillon? If you only want the code to execute when Gurinder Dhillon is using the file, then start with this line:
If Application.UserName <>= “Gurinder Dhillon” Then Exit Sub
Now, that will not solve the issue you are asking about but I wanted to mention a better way to go. Let me know what is erroring and what you expect to happen or not happen.
Thanks Tom.
I have figured it out and fixed the code. My next question is how do I adapt the code you provided above so that it takes into consideration when the sheet name is changed? In my code above it is named “RMPK Req”, but I have designed another code that updates the tab with the info typed in the description field. Also, a user may have multiple tabs of these so one file can have 3 or even 6 tabs of the same forms for different item and I would like to make sure that all mandatory fields are filled in otherwise they cannot save or close the file.
Also how do I adapt it if based on a value from a drop down, they can in fact save or close the file without having to fill out all mandatory field. IE. if they choose item detail change from the drop down all mandatory fields do not have to be filled out so they can just save it or close the file.
Hi Tom,
Thanks for the excellent work with the code. Currently I’m trying to set my worksheet with mandatory fields for use with colleagues to fill in work forms, however the data that gets entered is entirely up to the user.
So I have a few questions to ask:
1. When setting the cell range, because I never know how far down the user will go on the sheet, would setting the range as A2:A* work so that it will encompass all A column?
2. With question 1 in mind, if information has been entered into A60 but not A61, would the above range state that A61 is mandatory and has to be filled?
3. With both questions in mind, is there any code that would state that if row 61 is blank, but range A60:G60 and J60:P60 has been filled but H60 and I60 are missing, that these last 2 cells are mandatory but row 61 isn’t?
I know these questions can be confusing without a proper example sheet so I apologise in advance.
Thanks for all the help.
Jack
Hello Jack – –
The scenarios seem possible to code, but a few items need to be clarified first:
• Is this just for column A (speaking to your question #1), or is it for 16 columns, going out to column P (speaking to your question #3)?
• Is this code supposed to apply to just one worksheet in the workbook, or some worksheets in the workbook, or every worksheet in the workbook?
• Question #2 is strange. If A61 is mandated to be filled before closing the workbook, would it not mean by definition that if A61 were filled that cell A62, and so on, would need to be filled in? Where is the line drawn so that you don’t force your users to enter 1 million+ cells?
How can i make s cell a requirment to fill in if other cells are filled in?
You can adjust the code in this tip’s example to say (for example, using cell A1 as a cell that is filled in and cell B5 must then be filled in):
If Len(Range(“A1”).value) > 0 and len (Range(“B5”).value) = 0 Then
Modify for cell(s) and range(s) as needed.
I have this code entered to stop certain fields from saving blank and then a message appears… But now, I can’t save this with the “blanks” so I can pass on to my users. Is there apiece of code that I am missing? If so, can someone tell me what it is? And then exactly where I put it at?
CODE:
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
If Application.Sheets(“sheet1”).Range(“f12,f14,f16,h21:h23,f24:f26,f28,f32”).Value = “” Then
Cancel = True
MsgBox “Save Canceled,Required Field Blank”
End If
End Sub
Just guessing, if all blank cells need to be occupiued, I counted 11 cells in your code, so maybe
If worksheetfunction.counta(Sheets(“sheet1”).Range(“f12,f14,f16,h21:h23,f24:f26,f28,f32”)) <> 11 then
Maybe I misstated… It is working correctly with the code that I have entered. I just cant save it “blank” now to send on to the users so when they open it, it will be blank.
I have this code below to stop it from saving if there are certain cells left blank. If a required cell is left blank, I have a message to come up. — it is working perfectly.. The problem is that now, I cant save the document(with the required cells blank)to be able to send to users. The users are the ones that I want to have the messages pop up and not be able to save it blank. Can someone help?
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim MsgStr As String, A As Integer
A = 0
MsgStr = “”
If IsEmpty(ThisWorkbook.Sheets(1).Range(“f12”)) Then
MsgStr = MsgStr & “* Must enter Requestor E-mail Address” & vbCrLf
A = A + 1
End If
If IsEmpty(ThisWorkbook.Sheets(1).Range(“f14”)) Then
MsgStr = MsgStr & “* Must enter Recipient E-mail Address” & vbCrLf
A = A + 1
End If
If IsEmpty(ThisWorkbook.Sheets(1).Range(“f16”)) Then
MsgStr = MsgStr & “*Must enter policy number” & vbCrLf
A = A + 1
End If
If IsEmpty(ThisWorkbook.Sheets(1).Range(“h21”)) Then
MsgStr = MsgStr & “*Must enter Employee name” & vbCrLf
A = A + 1
End If
If IsEmpty(ThisWorkbook.Sheets(1).Range(“h22”)) Then
MsgStr = MsgStr & “*Must enter Employee SSN” & vbCrLf
A = A + 1
End If
If IsEmpty(ThisWorkbook.Sheets(1).Range(“h23”)) Then
MsgStr = MsgStr & “*Must enter Employee ID” & vbCrLf
A = A + 1
End If
If IsEmpty(ThisWorkbook.Sheets(1).Range(“F24”)) Then
MsgStr = MsgStr & “*Must enter GPC Code” & vbCrLf
A = A + 1
End If
If IsEmpty(ThisWorkbook.Sheets(1).Range(“f25”)) Then
MsgStr = MsgStr & “*Must enter Employer Name/Group” & vbCrLf
A = A + 1
End If
If IsEmpty(ThisWorkbook.Sheets(1).Range(“f26”)) Then
MsgStr = MsgStr & “*Must enter Paypoint” & vbCrLf
A = A + 1
End If
If IsEmpty(ThisWorkbook.Sheets(1).Range(“f28”)) Then
MsgStr = MsgStr & “*Must enter Years/Months as Employee or Member” & vbCrLf
A = A + 1
End If
If IsEmpty(ThisWorkbook.Sheets(1).Range(“f32”)) Then
MsgStr = MsgStr & “*Must enter Garage Bays” & vbCrLf
A = A + 1
End If
If A = 0 Then Exit Sub
If A = 8 Then Exit Sub ‘ Make A equal the number of IsEmpty Checks to Save an empty file
Beep
A = MsgBox(MsgStr, vbDefaultButton1, “Save Error”)
Cancel = True
End Sub
I’m a little bit lost here. Are you saying that if the cells of interest are empty when you want to save the file, you want to be allowed to do that, but anyone other than you cannot? There are workarounds for that but first I’m asking to be sure.
Exactly! If it is a code, I need that and need to know exactly where to put it. Thank YOU!!!!
To keep things simple because like most other tasks in life, this one can be complicated depending on network or workplace idiosyncrasies, first try this:
In the VBE press Ctrl+G which will open the Immediate window. Your cursor will be flashing in the Immediate window when you do that.
Type or paste this in:
? Application.UserName
Then press the Enter key
When I do that on my system, I get Thomas Urtis.
Whatever you get, be it Stacy Meile or whichever way your name is represented as the user of that computer, try wrapping an If statement around the code that says that if the user is not your name, then that can’t-save-or-close” code will be executed and enforced. Example:
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
If application.UserName <> “Your UserName” Then
the code
End If
End Sub
Hi,
Can I have a step by step procedure for this so people that I am going to have the file distributed, wont be able to save and close the file unless required fields are filled out.
Required fields are from J10 – J18
Say for a raw file that I have modified, I do not want anyone to leave J10 – J18 blank. I am new in using excel so I do not know where to start. Please give a me step by step instructions and if you can already modify the formula for me I will greatly appreciate it.
Follow the steps in my example, simply modify the code for the range you want to monitor.
Please read again my blog post where I already wrote every step you asked about.
Hello Tom,
Is there any other way than a specific user having the rights to save with empty data. Basically i dont want the following line… “If application.UserName “Your UserName” Then”
Rather than this, can the raw file be saved as a template that has certain cells empty and this template can be saved only after those certain cells filled with data?
If I understand your question correctly, could you use the BeforeSave event instead of the BeforeClose event?
Hi Tom,
here is my vb code,
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
If Application.Sheets(“demo”).Range(“C2”).Value = “” Then
Cancel = True
MsgBox “please fill in required field(s)”
End If
End Sub
code above works only for M2 cell.
I want whenever i put data in cell c2, then M2,S2,W2 become mandatory field (my workbook will allow users to be able to save, but if c2, or any of M2,S2,W2 is blank then users unable to save).
then same commands should apply for next row and so on once at a time.
thank you!
See if this does what you want:
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
If IsEmpty(Worksheets(“demo”).Range(“C2”)) = True Then
MsgBox “Enter something in cell C2”, , “C2 is empty.”
Cancel = True
Select Case True
Case IsEmpty(Range(“M2”)) = True
MsgBox “Enter something in cell M2”, , “C2 is empty.”
Cancel = True
Case IsEmpty(Range(“S2”)) = True
MsgBox “Enter something in cell S2”, , “C2 is empty.”
Cancel = True
Case IsEmpty(Range(“W2”)) = True
MsgBox “Enter something in cell W2”, , “C2 is empty.”
Cancel = True
End Select
End If
End Sub
Hi Tom,
Its a very great coding.
I have try to applied it for A1 and B1 cells and it was successful.
But I want to repeat the same check for entire column A and column B.
Can you please help me out ?
Its bit urgent.
Regards,
Abhishek Bhatt
Hi Tom-
I may have overlooked this but is there a way to prevent the save or close of an excel sheet unless a column total sum equals a set number? For example you need to pay out $200 between 5 people, which should be 40 each. However, you have mistyped a number and are off by $1. If this is not corrected, the file cannot be saved.
Thank you in advance!
Hi Alicia, yes this can be done, by placing the following code example (modify for column and expected amount) between the End If and End Sub statements in both original event codes I posted.
If WorksheetFunction.Sum( _
Worksheets(“Sheet1”).Columns(4) <> 100 Then
MsgBox “Workbook will not close unless” & vbCrLf & _
“the numbers in column D sum to $100.”, , “Column D not adding up.”
Cancel = True
End If
Note I have Columns(4) in the above example to represent column D. If it was column A, it would be columns(1) and if it was column L it would be columns(12).
Hi Tom,
Need your urgent help
I have some values in Column A lets A1:A100 , if i make any changes in Col A and if i leave Col B blank so workbook would not save,
Example 1 : -Means to say suppose if i change the value of A10 and do not update any thing (or keep blank) B10 so workbook will not save , if i write any thing on B10 so it allow to save workbook.
Example 2 – if i change the value of A15 and do not update any thing (or keep blank) B15 so workbook will not save , if i write any thing on B15 so it allow to save workbook.
Data range would be dynamic.
please help here as i need this,
Thanks’
What if, for example, you start with a value in A15 and no value in B15.
You delete what is in cell A15, meaning you changed A15.
But you left B15 empty.
Should the workbook be able to save?
Your question can actually get complicated for the logic needed. For example, suppose you change A1, then A2, then A3 just like that. The code would need to keep track of all changes you made, then keep track of what is in cell B1. B2, and B3. And, if you change, say, A2 back to what it was, BEFORE you make any changes to B2, what then?
I have a need for this VBA in one of my projects, thank you for offering it. I have a problem with utilizing it though; we rename the tab/worksheet from “Sheet 1” to another name and then that name is utilized to populate data within some cells on said tab/worksheet.
How can I run this macro if I don’t know what the new name of Sheet 1 will be? I do want to prevent users from saving the project without certain cells filled with data though.
Use the worksheet’s codename instead of its tab name.
Activate the sheet of interest.
Press ALT+F11 (Careful, that’s ALT+F11)
Press Ctrl+G
Type this in and hit Enter
? Activesheet.codename
You will see something like Sheet1 or Sheet5 or Sheet8.
Whatever it is, that is the sheet’s codename reference object you would use.
So for example, suppose that worksheet’s codename is Sheet4.
Instead of Worksheets(“Sheet1”).Range(“A1,A2,A3,A5,A5, A6”))
You would write
Sheet4.Range(“A1,A2,A3,A5,A5, A6”))
After that, people can change the worksheet’s tab name anything they want as often as they want, but the underlying object codename will always remain the same.
So the code will become:
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
If WorksheetFunction.CountA( _
Worksheets(Sheet1).Range(“A1,A2,A3,A5,A5, A6”)) < 6 Then
MsgBox "Workbook will not be saved unless" & vbCrLf & _
"All required fields have been filled in!", , "Missing info"
Cancel = True
End If
End Sub
Is that correct? Also, and sorry for the dumb question, I'm very new to VBA coding…what does the "<6" do? Is it simply checking that the cells have a value of more than 6?
I haven't been able to get this to run without getting a run time error, so thank you for helping me through it.
No, that’s not what the code will become. Please carefully re-read my last message about the required syntax.
The purpose of < 6 is to count how many of the 6 cells in your range of interest have some value in them. If the count of values is less than the count of (in your example, 6) cells, the workbook cannot be saved.
Thanks Tom, I used the code below and it’s displaying the error pop up box correctly now. I’m having difficulty keeping it for that way for other users once I put in an exception for myself (administrator) though. I reviewed the posts above but am unable to see the error I’m making. When I test the file on another workstation (that I’m not logged into), the file will go through the save process normally. If I remove the application username lines of code, the error message will pop up as intended. What am I doing wrong? Thanks for the help!
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
If Application.UserName = “Moz” Then
End If
Exit Sub
If WorksheetFunction.CountA( _
Sheet1.Range(“E4,B6,d6,h6,B8,E8, I8”)) < 7 Then
MsgBox "Workbook will not be saved unless" & vbCrLf & _
"All required fields have been filled in!", , "Missing info"
Cancel = True
End If
End Sub
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
If Application.UserName = “Moz” Then exit sub
If WorksheetFunction.CountA( _
Sheet1.Range(“E4,B6,d6,h6,B8,E8, I8”)) < 7 Then MsgBox "Workbook will not be saved unless" & vbCrLf & _ "All required fields have been filled in!", , "Missing info" Cancel = True End If End Sub
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
If Application.UserName = “Moz” Then Exit Sub
Im using the code listed above, and the application user is Moz, but the error popup box is still coming up when I try to save the file and the specified cells are empty. Am I missing something?
“when I try to save the file and the specified cells are empty.”
The specified (in your case) 7 cells must each have something in them, according to what the code you wrote says you want.
Sorry, I should have clarified. When the application user is Moz, and the specified cells are empty, I can not save the file.
Using the code
If Application.UserName = “Moz” Then Exit Subshould allow me (Moz) to use the file and save it even if the specified cells are empty, no?If Application.UserName = “Moz” Then
Application.enableevents = False
ThisWorkbook.Save
Application.enableevents = true
Exit sub
End If
The rest of the code goes here.
End Sub
I am trying to follow along with the thread, but for some reason I am having similar issues. I would like to have my file not save for all users except myself when certain cells are blank. It works great until I try to save and can’t, but i want the cells left blank. I keep trying to add the
If application.UserName = “username” Then Ext Sub, but it just keeps giving me an error. Here is the code if anyone could help fill in the missing pieces. Thank you
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
If IsEmpty(ThisWorkbook.Sheets(1).Range(“B12”)) Then
MsgBox (“Must enter Consultant Name or NONE”)
Cancel = True
Else
If IsEmpty(ThisWorkbook.Sheets(1).Range(“B13”)) Then
MsgBox (“Must enter Consultant Name or NONE”)
Cancel = True
Else
If IsEmpty(ThisWorkbook.Sheets(1).Range(“B14”)) Then
MsgBox (“Must enter Consultant Name or NONE”)
Cancel = True
Else
If IsEmpty(ThisWorkbook.Sheets(1).Range(“B15”)) Then
MsgBox (“Must enter Consultant Name or NONE”)
Cancel = True
Else
If IsEmpty(ThisWorkbook.Sheets(1).Range(“B16”)) Then
MsgBox (“Must enter Consultant Name or NONE”)
Cancel = True
Else
If IsEmpty(ThisWorkbook.Sheets(1).Range(“B17”)) Then
MsgBox (“Must enter Consultant Name or NONE”)
Cancel = True
Else
If IsEmpty(ThisWorkbook.Sheets(1).Range(“B18”)) Then
MsgBox (“Must enter Consultant Name or NONE”)
Cancel = True
End If
End If
End If
End If
End If
End If
End If
End Sub
There are a couple errors that appear to be in this you wrote:
“If application.UserName = “username” Then Ext Sub”
First, the code line should end with “Then Exit Sub”
Note the spelling correction for Exit.
Second, is this really what you have in your code?
If application.UserName = “username” ?
Or do you have your actual user name as opposed to “username” the way you posted it here?
I ask this because the chances are pretty good that your computer’s Excel user name is not “username”.
So, to get your user name, from any worksheet press Alt+F11 to go to the VBE, then Ctrl+G to go into the Immediate window, and type or paste in this, just as you see it here…
? Application.UserName
…and hit Enter.
Whatever pops up on the line below that is what your computer regards your user name to be.
Let’s say your username is Megan Zielinski for example.
That first line of code would be:
If application.UserName = “Megan Zielinski” Then Ext Sub
Try that and see if it does what you need.
Hi Thomas – thanks for the code above, super helpful. I know I am bringing up an old thread, but I am having trouble finding a solution to a twist on this code and thought I’d give this a shot.
I would like to have messages pop up on save if values were not entered (all good on this from above code), but I want to also give the user a chance to go ahead and close by a button click or something of that sort.
In my personal case, this is more to bring attention to user that they left questions blank, but allowing them to still close – realizing that they may not be able to fill out everything I need them to the first time they open the file (need info from other depts for example).
Appreciate any insight on this!
Hi Jordan, thanks for your question. I’m traveling this week and will be able to reply next week, just to let you know.
Hi Tom – no worries I appreciate the reply. I actually figured it out last night – and it was way more simple than I imagined. I just removed the “Cancel = True” & it worked exactly as I hoped.
Have a good day
Thanks, Jordan!
Hi Tom,
Firstly thank you for taking the time to answer so many questions and hopefully mine. Like some others I have zero knowledge on VBA but following your examples I have a sheet which works, BUT I want to expand the functionality and I’m not sure if it’s possible ( or actually feasible).
I have a spreadsheet with multiple sheets which is sent to multiple users in multiple countries.
I require a specific user from each country to complete a specific cell entry. (If they don’t they can’t close the spreadsheet). The sheet should allow each user to save the file with missing data from the other users.
eg Sherlock Holmes must complete a cell B2 for England, Corinne Lautrec cell B3 for France, Gunnar Ericsson cell B4 for Sweden. The sheet would look simplistically like this:-
A1 Question 1
A2 England Answer in B2
A3 France Answer in B3
A4 Sweden Answer in B4
Kind regards Roger
Hello Roger – –
What you want to do is possible. It does take a bit of housekeeping, depending on your project’s end users, and how many multiple users there are. The precise number of users does not matter, but the general range of user count would influence which direction I would go.
The trick is to identify each user with which cell they are respectively responsible to fill in. The problem usually arises when users work on more than one computer or device, such as at work and at home, and maybe on their tablet on the train to and from work. Therefore, identifying each user by their device’s username property is not reliable, nor is identifying their computer’s hard drive serial number, in order to ensure a 1 to 1 match that corresponds to the cell they need to fill in. If it were guaranteed that each user will always work on just 1 device, or that each user will have a unique name (in other words, no two people with the username “John Smith”) then a computer’s username ID would be reliable. But in the real world, those situations are rarely the case.
So, if it were me, I would insert a 3-column hidden table in the workbook, or more preferably in a separate file somewhere that the workbook would look up, and in that table match the user’s name with their password that I would also mandate at the workbook’s open event. That way, the 3 items (user name, password, and cell address of completion requirement) would be unique to that user and the user would only be burdened with that cell’s completion requirement.
If (usually when) a new person is added to the project with a new cell address to be responsible for, you can add that record to the table, which is more of a reason to have the table be external, in a place where you can edit it and no one else can see it.
Based on your question and how I understand it, that would be my suggestion.
Hi Tom,
Thank you for your thoughts and suggestions, it is greatly appreciated.
I’ll give it a go and probably succeed around the time the report is no longer required! :oD
Kind regards
Roger
Hi Tom,
If i have multiple conditions to fulfil, how could i have code it?
If Column A is not empty, Column B to D must be filled up, else it cannot close.
If Column A is not empty and either one of the column B to D is empty, then highlight the row in red colour.
Hope you can help.
Thank you.
Regarding your first question, try this in your worksheet module, modifying for sheet name:
Private Sub Workbook_BeforeClose(Cancel As Boolean)With Worksheets("Sheet1")
Select Case True
Case WorksheetFunction.CountA(.Columns(1)) <> WorksheetFunction.CountA(.Columns(2))
MsgBox "Column A and Column B do not match."
Cancel = True
Case WorksheetFunction.CountA(.Columns(1)) <> WorksheetFunction.CountA(.Columns(3))
MsgBox "Column A and Column C do not match."
Cancel = True
Case WorksheetFunction.CountA(.Columns(1)) <> WorksheetFunction.CountA(.Columns(4))
MsgBox "Column A and Column D do not match."
Cancel = True
End Select
End With
End Sub
Regarding your second question, select cell B1 to D whatever and use this CF rule:
=AND(LEN($A1)>0,LEN(B1)=0)Hi Tom,
I’m new to VBA. I have gone through many other blogs but, found this channel as very helpful and active. can you please help Me in the below requirements.
Thanks in advance.
We need to restrict user for save and close to ensure the below requirements and need to handle all the rows in the respective columns.
1a. Columns ‘A’, ‘B’, ‘C’, ‘D’, ‘E’, ‘F’, ‘G’ and ‘I’ are mandatory. User needs to provide data in
this columns else we need to restrict user from closing and saving excel.
1b. Need to handle all the cells and need to ask user to Provide missing value in corresponding cell
of a column let’s say ‘CELLxyz’ of column ‘COLUMNabc’ respectively.
2a. Column ‘A’ should have unique values(we need to generate unique values) else it should throw an
error for the corresponding cell with duplicate values.
Let’s say ‘CELLxyz’ in ‘COLUMN A’ have duplicate values.
2b. Requirement is to automatically generate the below sequence from ‘A1’ to ‘An’
Example: XYZ1000
XYZ1001
XYZ1002
The sequence for ‘Column A’ should begin with XYZ1000.
2c. If user tries to enter data excluding the above sequence then we need to restrict.
2d. When user tries to create or insert a new record the next number should be populated in Column A
automatically i.e XYZ1003.
3. Columns ‘G’, ‘N’, ‘O’, ‘P’ are drop down lists with ‘Yes’ and ‘No’ options.
Regards,
Kiran
Hello Kiran – –
Thanks for your comment. It seems do-able but there is a good bit of logic in there that is not complex but would take a bit of time for me to sift through. Especially this month, I am tied up with my clients’ projects. May I suggest you ask this at a popular Excel forum that I also contribute to when I have the time, at https://www.mrexcel.com/board/forums/excel-questions.10/. It’s free and easy to become a member, with experts around the clock who can help you. If you don’t have an answer from them in a couple weeks (but I bet you will), please do post back and I can help you at that time.
Hi Tom,
I’m a newbie, and I’ve gone through and tried some of these above replies, but no luck adding in the saving as template – using the application.UserName workaround.
I confirmed my username with “? Application.UserName” – no problem.
Here’s what i’m trying to add it to (which works fine, except for trying to save it as a blank template). BeforePrint & beforeSave. I either get a yellow highlight over the “Private Sub line” or red text and selector on my last name.
Private Sub Workbook_BeforePrint(Cancel As Boolean)
If Worksheets(“Order Details”).Cells(1, 2).Value = “Year” Then
MsgBox “Order Details; Cell B1(Year) requires user input”
Cancel = True
End If
If Worksheets(“Order Details”).Cells(2, 2).Value = “Qtr” Then
MsgBox “Order Details; Cell B2(Quarter) requires user input”
Cancel = True
End If
If Worksheets(“Order Details”).Cells(3, 2).Value = “” Then
MsgBox “Order Details; Cell B3(Vendor) requires user input”
Cancel = True
End If
If Worksheets(“Order Details”).Cells(4, 2).Value = “Product Type” Then
MsgBox “Order Details; Cell B4(Product Type) requires user input”
Cancel = True
End If
If Worksheets(“Order Details”).Cells(5, 2).Value = “” Then
MsgBox “Order Details; Cell B5(Order Date) requires user input”
Cancel = True
End If
End Sub
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
If Worksheets(“Order Details”).Cells(1, 2).Value = “Year” Then
MsgBox “Order Details; Cell B1(Year) requires user input”
Cancel = True
End If
If Worksheets(“Order Details”).Cells(2, 2).Value = “Qtr” Then
MsgBox “Order Details; Cell B2(Quarter) requires user input”
Cancel = True
End If
If Worksheets(“Order Details”).Cells(3, 2).Value = “” Then
MsgBox “Order Details; Cell B3(Vendor) requires user input”
Cancel = True
End If
If Worksheets(“Order Details”).Cells(4, 2).Value = “Product Type” Then
MsgBox “Order Details; Cell B4(Product Type) requires user input”
Cancel = True
End If
If Worksheets(“Order Details”).Cells(5, 2).Value = “” Then
MsgBox “Order Details; Cell B5(Order Date) requires user input”
Cancel = True
End If
End Sub
———————————————————–
I have tried adding in
If Application.UserName = “Darryl Lastname” Then
Application.enableevents = False
ThisWorkbook.Save
Application.enableevents = true
Exit sub
End If
The rest of the code goes here.
End Sub
——————————————————
as well as
If Application.UserName = “Darryl Lastname” Then Exit Sub
——————————————————–
I’m thoroughly confused lol. If you have any thoughts or solutions – greatly appreciated.
Thank you,
Darryl
My first suspicion and question is, because you said that you are a newbie, where exactly are you placing this code? It does NOT go into a standard module such as macros and User-Defined Functions would go. This is a workbook level event procedure and as such it goes into the workbook module. From your worksheet press Alt+F11, then press Ctrl+R to find your workbook name in the “Project – VBAProject” window. Expand the Microsoft Excel Object folder for your workbook, right click on ThisWorkbook and left-click to select View Code. Paste the procedure into the large white area that is the workbook module, then press Alt+Q to return to the worksheet.
Maybe you already did this, but since you did not specifically say that you did, it is my first likely suspect to at least rule out as the reason for the difficulty.
Hi. Yes, it’s placed in VBAProject/Microsoft Excel Objects/This Workbook
I get a Syntax error highlighting the line [ If Application.UserName = “Darryl Lastname” Then ]
And if I make a subtle change to [ If Application.username = “Darryl Lastname” Then ] it gives me a compile error “Expected: Then or GoTo”
—————————————————
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
If Application.UserName = “Darryl Lastname” Then
Application.EnableEvents = False
ThisWorkbook.Save
Application.EnableEvents = True
Exit Sub
End If
If Worksheets(“Order Details”).Cells(1, 2).Value = “Year” Then
MsgBox “Order Details; Cell B1(Year) requires user input”
Cancel = True
End If
If Worksheets(“Order Details”).Cells(2, 2).Value = “Qtr” Then
MsgBox “Order Details; Cell B2(Quarter) requires user input”
Cancel = True
End If
If Worksheets(“Order Details”).Cells(3, 2).Value = “” Then
MsgBox “Order Details; Cell B3(Vendor) requires user input”
Cancel = True
End If
If Worksheets(“Order Details”).Cells(4, 2).Value = “Product Type” Then
MsgBox “Order Details; Cell B4(Product Type) requires user input”
Cancel = True
End If
If Worksheets(“Order Details”).Cells(5, 2).Value = “” Then
MsgBox “Order Details; Cell B5(Order Date) requires user input”
Cancel = True
End If
End Sub
End Sub
To make sure we are both clear with your actual code, and just for starters to attack this one issue at a time, now that it is verified to be in the proper module, you said your code looks like this:
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) If Application.UserName = “Darryl Lastname” Then Application.EnableEvents = False ThisWorkbook.Save Application.EnableEvents = True Exit SubHow that portion of the code should look is with certain commands on separate lines, like this:
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
If Application.UserName = "Darryl Lastname" Then
Application.EnableEvents = False
ThisWorkbook.Save
Application.EnableEvents = True
Exit Sub
End If
That worked! It appeared virtually the same – except I did have some lowercase in “Application.enableevents = False” and “Application.enableevents = True”, i’m not 100% certain that was part of the problem, but it works now.
Thank you so much!
Cheers,
Darryl
Happy to help.
So you know, the case (lower and upper) with the examples you cited made no difference.
Hi,
I’m a newbie,
I do have the range of the columns from I5 to AR40,
How to write a range for it?
I Have given range(“I5:AR40”) throwing an error as its wrong, but is there any alternate to make it easy way?
There are 1296 cells in range I5:AR 40, so assuming this range is on Sheet1 (modify the code if it is a different sheet name) then this should do what you want. Be sure to put it into the workbook module, which is usually named ThisWorkbook.
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
If WorksheetFunction.CountA( _
Worksheets("Sheet1").Range("I5:AR40")) < 1296 Then MsgBox "Workbook will not be saved unless" & vbCrLf & _ "All required fields have been filled in!", , "Missing info" Cancel = True End If End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)
If WorksheetFunction.CountA( _
Worksheets("Sheet1").Range("I5:AR40")) < 1296 Then MsgBox "Workbook will not be closed unless" & vbCrLf & _ "All required fields have been filled in!", , "Missing info" Cancel = True End If End Sub
Hi Tom, I am creating a form using an excel. And I have applied some drop down list in mandatories fields. Some mandatory fields are in free text form. And depending on the applicant, sometimes, will required 10 lines, sometimes only 1 line. I tried applying your code, but it didn’t work. May I know how can I work around with it to make it work?
Hello.
Just so I am clear when you say you are “creating a form using an excel”, do you mean you are creating this form on a worksheet in worksheet cells, or are you doing this on a UserForm.
Also, what do you mean, “Some mandatory fields are in free text form”? What is free text form?
I mostly wonder why it would matter at all if the user enters a single text line, or 10 text lines, or enters a single character in the cell. Either way, the cell would be filled because it is not empty. Please explain why it makes a difference to you as to how much text gets inputted to a cell.
I have a workbook with two worksheets. I’m using Excel 2016. I have conditional formatting which makes cells turn red if a certain drop down is chosen in a certain cell. The cell turns red until something is entered in the cell. Once anything is entered in the cell the red fill turns off. How do I prevent saving the file if a cell is red on either worksheet?
You could (which I would do) program the situation to evaluate as true or false for the condition you speak of, or (which basically is the same conceptual approach) evaluate the precedent drop down cell for it containing or not containing whichever value(s) are present that would have triggered the condition. Not knowing or seeing your workbook or cells of interest, that’s the best theoretical answer I can offer.
Good Evening,
Tom, first and foremost, do you have a site for buying you a cup of coffee or a nice donation?? I don’t mind throwing a few bucks for the help your giving all of us Less Excel Gurus.
What I am trying to do:
– When certain fields (“C3:C18,C20:C28,C30:C32,C34:C40,C42:C45,C47:C49,C51:C61,C63:C73,C75:C77”)) equal “” then associated field next to it becomes mandatory on save, otherwise, it can’t be saved.
I’ve been able to get a nice popup on change of the field in the “C” ranges, but that really doesn’t make them fill out the mandatory field.
Thanks for any hints or tips and how to throw a few bucks at ya.
Hello, Will, thanks so much for your compliments and offer. Instead of a donation which I appreciate your suggestion of, I have my first collection for sale (soon to be many more) of 100 interactive Excel workbooks that show great examples of VBA and formula solutions. It is only $10 and available here:
https://www.atlaspm.com/product/toms-tips-for-excel-volume-1-workbooks-1-to-100/
Your question sounds do-able but I am not sure based on your description what exactly you need.
For example, you said if certain fields equal “” then the associated field next to it becomes mandatory on save. First, I assume that “becomes mandatory” means there is a value in those cells. But that leads to 4 other questions:
(1) Do all cells in the associated range (I assume D3:D18 for C3:C18) need to hold a value, or do any one of them only need to hold a value.
(2) If C3 is empty but D7 has a value, and all the other cells are empty, that means one cell in each range has a value, but not on the same row, so what then.
(3) What if any cells in C3:C18 = “” and other cells in C3:C18 don”t.
(4) What does “” really mean to you? Sometimes it means the cell does not show a value. BUT, if a cell in C3:C18 has a formula that can (and sometimes does) return a null string, that means the cell is not empty because a formula is in it, but no value is visible.
Basically if you can explain more fully what you want based on all these possible scenarios, I can suggest a worksheet level event procedure that you can use.
Thanks for the response Tom.
I will get that ordered after this message.
So, we have a questionnaire that we ask yes or no questions. If they answer no, then the field next to it highlights red (Conditional rule) and then we want that field to be mandatory on save. If they do not enter anything into that field and try to save again, that same popup pops up again… not allowing them to save.
Right now we have the below, but it is NOT what we want. It doesn’t make the field mandatory, it just pops up when the field is changed from nothing to something else. IE From Nothing to “No” or “X” Example screen shot here — > https://ibb.co/yg78JtG Hopefully this will show you more. A picture is worth a thousand words.
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Not Intersect(Target, Range(“C3:C18,C20:C28,C30:C32,C34:C40,C42:C45,C47:C49,C51:C61,C63:C73,C75:C77”)) Is Nothing Then
MsgBox (“Please fill out Notes/Description Section”)
Else
End If
End Sub
So what needs to have an accompanying value is for column E based on the picture.
Is there, or might there ever be, more than one worksheet in this workbook, and if the answer to that question is yes, what is the name of the worksheet where this is happening.