Tom’s Tutorials For Excel: Preventing Save or Close Unless Cells are Filled

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 End If End Sub

Share Button
Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , ,
66 comments on “Tom’s Tutorials For Excel: Preventing Save or Close Unless Cells are Filled
  1. Jason says:

    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

    • Tom Urtis says:

      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.

      • Khairul says:

        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

        • Tom Urtis says:

          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.

  2. Brandon says:

    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

    • Tom Urtis says:

      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.

  3. Suzanne Burton says:

    Hi Tom,

    Would this still work say if my colleague did Save As?

  4. Lulu says:

    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!

    • Tom Urtis says:

      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.

  5. Mirjam says:

    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

    • Tom Urtis says:

      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.

      • Katherine says:

        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.

  6. Kim McKergow says:

    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

  7. Kim McKergow says:

    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

  8. Katherine says:

    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

    • Tom Urtis says:

      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.

      • Katherine says:

        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.

        • Katherine says:

          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.

  9. Jack says:

    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

    • Tom Urtis says:

      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?

  10. D. Rosario says:

    How can i make s cell a requirment to fill in if other cells are filled in?

    • Tom Urtis says:

      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.

  11. Stacy Meile says:

    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

    • Tom Urtis says:

      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

  12. Stacy Meile says:

    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.

  13. Stacy Meile says:

    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

    • Tom Urtis says:

      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.

  14. Stacy Meile says:

    Exactly! If it is a code, I need that and need to know exactly where to put it. Thank YOU!!!!

    • Tom Urtis says:

      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

  15. Willie Jhune Neria says:

    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

    • Willie Jhune Neria says:

      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.

    • Tom Urtis says:

      Please read again my blog post where I already wrote every step you asked about.

  16. Sudhakar says:

    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?

  17. Florid Linus says:

    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!

    • Tom Urtis says:

      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

  18. Abhishek Bhatt says:

    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

  19. Alicia says:

    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!

    • Tom Urtis says:

      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).

  20. Rahul Rawat says:

    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’

    • Tom Urtis says:

      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?

  21. Moz says:

    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.

    • Tom Urtis says:

      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.

      • Moz says:

        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.

        • Tom Urtis says:

          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.

  22. Moz says:

    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

    • Tom Urtis says:

      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

      • Moz says:

        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?

        • Tom Urtis says:

          “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.

          • Moz says:

            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 Sub should allow me (Moz) to use the file and save it even if the specified cells are empty, no?

            • Tom Urtis says:

              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

  23. Megan Zielinski says:

    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

    • Tom Urtis says:

      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.

  24. Jordan B says:

    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!

  25. Roger says:

    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

    • Tom Urtis says:

      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.

  26. Roger says:

    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

  27. Ming says:

    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.

    • Tom Urtis says:

      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)

  28. Kiran says:

    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

    • Tom Urtis says:

      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.

Leave a Reply

Your email address will not be published. Required fields are marked *

*