·
7 months ago
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.
Any help?
2 sets of ranges are here but under the same column.
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
|
| Kutools for Excel's Protect Worksheet function can quickly protect multiple sheets or the whole workbook at once. |
Office Tab Enable Tabbed Editing and Browsing in Office, and Make Your Work Much Easier...
Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by
80%
- Reuse Anything: Add the most used or complex formulas, charts and anything else to your favorites, and quickly reuse them in the future.
- More than 20 text features: Extract Number from Text String; Extract or Remove Part of Texts; Convert Numbers and Currencies to English Words.
- Merge Tools: Multiple Workbooks and Sheets into One; Merge Multiple Cells/Rows/Columns Without Losing Data; Merge Duplicate Rows and Sum.
- Split Tools: Split Data into Multiple Sheets Based on Value; One Workbook to Multiple Excel, PDF or CSV Files; One Column to Multiple Columns.
- Paste Skipping Hidden/Filtered Rows; Count And Sum by Background Color; Send Personalized Emails to Multiple Recipients in Bulk.
- Super Filter: Create advanced filter schemes and apply to any sheets; Sort by week, day, frequency and more; Filter by bold, formulas, comment...
- More than 300 powerful features; Works with Office 2007-2019 and 365; Supports all languages; Easy deploying in your enterprise or organization.
Make a cell mandatory enter with VBA
| Kutools for Excel, with more than 120 handy Excel functions, enhance your working efficiency and save your working time. | ||
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
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:
Tip: You can change cell B1 to other cells you want.
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.
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!
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment...
The comment will be refreshed after 00:00.
-
To post as a guest, your comment is unpublished.
-
To post as a guest, your comment is unpublished.
· 2 years agoIs there any way that I can Required cell f4 and F5 have data before closing or saving?
-
To post as a guest, your comment is unpublished.
· 2 years agoJust change B1 to F4 and F5 in the textbox to remind users.
-
-
To post as a guest, your comment is unpublished.
· 2 years agoHello, 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!-
To post as a guest, your comment is unpublished.
· 4 months agoI have this same issue. Is there a way to make a cell mandatory but blank for a template?
-
To post as a guest, your comment is unpublished.
· 8 months agoI'm interested in this also, thanks!
-
To post as a guest, your comment is unpublished.
· 1 years agoAny update on this?
-
-
To post as a guest, your comment is unpublished.
· 2 years agoDid you ever fined an answer to this?
-
To post as a guest, your comment is unpublished.
· 2 years agoSorry, which question do you mean?
-
-
To post as a guest, your comment is unpublished.
· 3 years agohow 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-
To post as a guest, your comment is unpublished.
· 2 years agoHello, 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
-
한국어
English
العربية
简体中文
繁體中文
Nederlands
Français
Deutsch
Italiano
日本語
Português
Русский
Español