Note: The other languages of the website are Google-translated. Back to English
English English▼
X
en Englishar Arabichy Armenianzh-CN Chinese (Simplified)zh-TW Chinese (Traditional)cs Czechda Danishnl Dutchfr Frenchde Germanel Greekhu Hungarianid Indonesianga Irishit Italianja Japaneseko Koreanpl Polishpt Portuguesero Romanianru Russiansl Slovenianes Spanishsv Swedishth Thaitr Turkishuk Ukrainianvi Vietnamesecy Welsh

How to make a particular cell mandatory enter before closing workbook?

When you share a workbook to other users for doing a survey which needs the real name registration, for instance, every user under investigation needs to enter their name in B1. But in some times, some users may close the workbook after investigation without entering their names. In this article, I introduce a VBA to make a particular cell mandatory before closing the workbook.

Make a cell mandatory enter with VBA

arrow blue right bubble Make a cell mandatory enter with VBA

1. Enable the workbook that contains the mandatory cell, and press Alt + F11 keys to open Microsoft Visual Basic for Applications window.

2. In the Project pane, double click ThisWorkbook, and go to select Workbook and BeforeClose from right section list, then paste below code to the script. See screenshot:

VBA: Make a cell mandatory

    If Cells(1, 2).Value = "" Then
        MsgBox "Cell B1 requires user input", vbInformation, "Kutools for Excel"
        Cancel = True
    End If

doc mandantory enter 1

3. Then save this code and close this popping window. Now, if the cell B1 is blank when you close the workbook, below dialog will pop out to remind you to enter something in B1. See screenshot:
doc mandantory enter 2

Tip: You can change cell B1 to other cells you want.

The Best Office Productivity Tools

Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%

  • Reuse: Quickly insert complex formulas, charts and anything that you have used before; Encrypt Cells with password; Create Mailing List and send emails...
  • Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
  • Merge Cells/Rows/Columns without losing Data; Split Cells Content; Combine Duplicate Rows/Columns... Prevent Duplicate Cells; Compare Ranges...
  • Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
  • Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
  • Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
  • Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
  • Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
  • More than 300 powerful features. Supports Office/Excel 2007-2019 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features 30-day free trial. 60-day money back guarantee.
kte tab 201905

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!
officetab bottom
Comments (27)
No ratings yet. Be the first to rate!
ashley
ashley
This comment was minimized by the moderator on the site
how do i make column D16-D300 mandatory if column B16-300 has values? I used the code below for row 16 and tried variations but it is not working. any help is greatly appreciated! 'us business as a mandatory field- need to fix if none are there and window pops up Private Sub Workbook_BeforeClose(Cancel As Boolean) 'Step 1: Check to see if Cell A1 is blank If Sheets("Sensitive Leave Tracker").Range("B16").Value "" And Sheets("Sensitive Leave Tracker").Range("D16").Value = "" Then 'Step 2: Blank: cancel the Close and tell the user Cancel = True MsgBox "Cant close field required" 'Step 3: Not Blank; Save and Close Else ActiveWorkbook.Close SaveChanges:=True End If End Sub
Reply
0
0
Sunny
Sunny     ashley
This comment was minimized by the moderator on the site
Hello, sorry to reply so late. Here is the macro code you may be need. If it works, please let me know. Thanks!

If Application.WorksheetFunction.CountA(Range("B16:B300")) > 0 Then

If Application.WorksheetFunction.CountA(Range("D16:D300")) <> Range("D16:D300").Count Then

MsgBox "Cell D16:D300 requires user input", vbInformation, "Kutools for Excel"

Cancel = True

End If

End If
Reply
Report
0
0
Vishal Bhagwan Kadam
Vishal Bhagwan Kadam     Sunny
This comment was minimized by the moderator on the site
how do i make column Q7-Q500 mandatory if column B7-B500 has values

I don't want to save flle before completing mandatory filed.

eg.
If I put any value / Text in column D7 to D500 then Column Q7 to Q500 should be Mandatory. (window Pop up)
If i didn't put any value/text in column D7 to D500 then Q7 to Q500 should be empty
Reply
0
0
Sunny
Sunny     Vishal Bhagwan Kadam
This comment was minimized by the moderator on the site
Hi, try above VBA and change the reference to you own need.
Reply
Report
0
0
Shabaaz
Shabaaz
This comment was minimized by the moderator on the site
Did you ever fined an answer to this?
Reply
0
0
Sunny
Sunny     Shabaaz
This comment was minimized by the moderator on the site
Sorry, which question do you mean?
Reply
Report
0
0
zwaenepoel
zwaenepoel
This comment was minimized by the moderator on the site
Hello, thank you for this formula, it works!

However, now I have created a template in which several cells are mandatory to fill out. But after creating the template and entering the macro, I cannot close it, because I have to fill out the cells :) So, I cannot send out a template...(where the mandatory cells must still be empty)


Can you help me?

Thanks!
Reply
Report
0
0
zagreb
zagreb     zwaenepoel
This comment was minimized by the moderator on the site
Any update on this?
Reply
0
0
M
M     zwaenepoel
This comment was minimized by the moderator on the site
I'm interested in this also, thanks!
Reply
0
0
Sally
Sally     zwaenepoel
This comment was minimized by the moderator on the site
I have this same issue. Is there a way to make a cell mandatory but blank for a template?
Reply
0
0
Rick
Rick     zwaenepoel
This comment was minimized by the moderator on the site
Hi everybody, I found the solution was to pause the macro in the alt+f11 screen. It can then be saved and will restart when next opened. To do this press alt + f11 then in the row of icons above the window (same one as where the little blue file disk icon is) you will see Play, Pause, Stop icons in green and blue (should be under the Debug and Run options). Click the middle pause button then you can save.
Reply
0
0
Ricky I
Ricky I     zwaenepoel
This comment was minimized by the moderator on the site
Hi everybody, I found the solution was to pause the macro in the alt+f11 screen. It can then be saved and will restart when next opened. To do this press alt + f11 then in the row of icons above the window (same one as where the little blue file disk icon is) you will see Play, Pause, Stop icons in green and blue (should be under the Debug and Run options). Click the middle pause button then you can save.
Reply
Report
0
0
Sunny
Sunny     zwaenepoel
This comment was minimized by the moderator on the site
Hi, try below code, which will force other users to enter value in range A7:M7 before closing workbook, but allow yourself to colse the workbook without filling.
Notice: change the sunflower to your own username. To find and edit your user name, click File > Options > General > User Name.
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim rg As Range
Dim rgs As Range
Set rgs = Application.Range("A7:M7")
If Application.UserName = "sunflower" Then
Exit Sub
End If
    For Each rg In rgs
        If rg.Value = "" Then
        MsgBox "Range A7:M7 requires user input", vbInformation, "Kutools for Excel"
        Cancel = True
        Exit Sub
       End If
    Next
End Sub

Hope this code can help you.
Reply
Report
0
0
Shea
Shea
This comment was minimized by the moderator on the site
Is there any way that I can Required cell f4 and F5 have data before closing or saving?
Reply
0
0
Sunny
Sunny     Shea
This comment was minimized by the moderator on the site
Just change B1 to F4 and F5 in the textbox to remind users.
Reply
Report
0
0
Akshay Patil
Akshay Patil
This comment was minimized by the moderator on the site
I want to make rows C2 to C7 mandatory and C13 to C19 mandatory?

Any help?

2 sets of ranges are here but under the same column.
Reply
1
0
raman sharma
raman sharma
This comment was minimized by the moderator on the site
i want to make the tick marks mandatory in sheet
Reply
0
0
Craig
Craig
This comment was minimized by the moderator on the site
Is there a way to make a cells input mandatory before an Active X command button can be used? I have a command button setup to submit a form via email but want to make certain cells mandatory before the button will submit the email.
Reply
0
0
Sunny
Sunny     Craig
This comment was minimized by the moderator on the site
Hi, Craig, sorry this problem cannot be solved here, maybe other else can help you.
Reply
Report
0
0
Lester
Lester
This comment was minimized by the moderator on the site
Question, the code worked, but how can choose a range from A7:M7 up to the last row?

and whenever the user will save or close the workbook, the mandatory field have to be filled out
Reply
0
0
Sunny
Sunny     Lester
This comment was minimized by the moderator on the site
Hi, Lester, here is a code we modified may help you, change the range A2:F2 to the range as you need.
Private Sub Workbook_BeforeClose(Cancel As Boolean)
'UpdatebyExtend20220916
Dim xRgCount As Integer

Dim xURg As Range

Dim xStr As String

Dim xWSh As Worksheet

Dim xRg, xRg1, xRg2, xRg3 As Range

xStr = "A2:F2"

 

Set xWSh = Application.ActiveSheet

Set xRg = xWSh.Range(xStr)

Set xURg = xWSh.UsedRange

Set xRg1 = xWSh.Cells.Item(xRg.Row + xRg.Rows.Count, 1)

Set xRg2 = xURg.Item(xURg.Count)

Set xRg3 = xWSh.Range(xRg1, xRg2)

Debug.Print xRg3.Address

If (xURg.Row + xURg.Rows.Count - 1) > (xRg.Row + xRg.Rows.Count - 1) Then

   If Application.WorksheetFunction.CountA(xRg3) <> xRg3.Count Then

    MsgBox "There are blank cells under range A2:F2", vbInformation, "Kutools for Excel"

Cancel = True

   End If

End If

End Sub
Reply
Report
0
0
Lester
Lester
This comment was minimized by the moderator on the site
Is it possible to create a code in a new generated workbook? the scenario is, I will generate a new workbook, and that generated workbook must have a mandatory field to be filled, the user cannot save unless it is not filled the mandatory field. I have already created, generated a new workbook. my problem is, the new generated workbook does not have a code.
Reply
0
0
Emma
Emma
This comment was minimized by the moderator on the site
Is het mogelijk om als regel erin te zetten dat als C1 een bepaalde tekst bevat dan D1 ook ingevuld moet worden. Dit moet gelden voor circa 2.000 regels. Het bestand wordt elke keer aangevuld dus het moet alleen gelden voor het aantal regels die nu zijn ingevuld.

Wanneer ik volgende functie erin zet werkt het niet zoals ik wil omdat ik dan meldingen krijg van cellen die nog aangevuld moeten worden en ik wil een tekst als waarde en geen cijfer.

Als Application.WorksheetFunction.CountA(Range("B16:B300")) > 0 then

If Application.WorksheetFunction.CountA(Range("D16:D300")) <> Range("D16:D300").Count Then

MsgBox "Cell D16:D300 vereist gebruikersinvoer", vbInformation, "Kutools for Excel"

Annuleren = Waar

End If

End If

Gr emma
Reply
0
0
PM
PM
This comment was minimized by the moderator on the site
Can I email you document so you can best advise me how to implement VBA code so file cannot save if each row is not fully populated? Mine is a bit tricky. Will appreciate the help, been trying for so long.
Reply
0
0
Sunny
Sunny     PM
This comment was minimized by the moderator on the site
Hi, here is a code, it will pops a dialog to remind the user which cell is needed entered, and the workbook cannot be closed and saved until all cells in the range a1:c3 are all filled with contents.
Please modify the script in the code:
1. change the range as you need,
2. change the the user name "Kutools for Excel" in the code to your own username (please make sure other user has no the same user name with you)

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    
    If Application.UserName <> "Kutools for Excel" Then

        Set Rg = Sheets("Sheet2").Range("a1:c3")

        If WorksheetFunction.CountBlank(Rg) > 0 Then

            MsgBox Rg.Parent.Name & " " & Rg.Address & " requires user input", vbInformation, "Kutools for Excel"
            Cancel = True
        End If
    End If

End Sub
Reply
Report
0
0
PM
PM
This comment was minimized by the moderator on the site
Sorry Sir,

For somebody using VBA for first time, can you please provide some guidance on how to initiate this? I want each row to have same feature I.E. (until one completes all info on row)
Reply
0
0
Sunny
Sunny     PM
This comment was minimized by the moderator on the site
Hi, PM, just follow the steps in this article, then replace the code with the code I provided for you, and then save it. And when you share the workbook to others whose username are different with you, they will be asked to fill every cell in the range (a1:c3, you can change it in the code) until they fill all.
Reply
Report
0
0
There are no comments posted here yet
Leave your comments
Guest
Posting as Guest
Login
×
Rate this post:
Reset
0   Characters
Suggested Locations