Making Certain Columns Mandatory & Unique. Restrict, if user fails to maintain values in mandatory columns & unique values before Save and Close

Kiran2229Kiran

New Member
Joined
Jul 15, 2020
Messages
2
Office Version
  1. 2016
Platform
  1. Windows
Hello,

I am new to VBA. I have gone through many other blogs but, found this channel very helpful and active. I need to fulfill this requirements. 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 these 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 us say ‘CELLxyz’ of column ‘COLUMNabc’ respectively.

2a. 'Column A’ should have unique values (we need to generate or propose unique values) else it should throw an error for the corresponding cell with duplicate values.

Let us say ‘CELLxyz’ in ‘COLUMN A’ have duplicate values.

2b. Requirement is to automatically generate the below sequence in 'Column A' 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.


Regards,
Kiran
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
I would normally approach this using simple conditional formatting of cells in mandatory columns to highlight if no data has been entered.
Using a 'helper' column to the right I would add a formula to check that all mandatory cells contain at least some data (you might be able to use data validation on these cells to try and ensure that only valid data has been entered such as restricting entry to dates etc.

The following code entered in the 'ThisWorkbook' module will prevent the book from being saved if any row has the 'Missing Data' warning

VBA Code:
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim NoErrors As Integer
    NoErrors = Application.WorksheetFunction.CountIf(Range("J:J"), "Missing Data")
    If NoErrors = 0 Then
        Exit Sub
    Else
    Cancel = True
    MsgBox "SAVE CANCELLED - Please correct the missing data as indicated in column J"
    End If
End Sub

In column A you could format the cells to have a custom format of say "XYZ"0 and enter the value of 1000 in cell A2 and in A3 enter formula =A2+1, then, if your data is formatted as a table, any new rows added will automatically add the next sequential number in column A.

See below for an example of what I have tried for you.

Book1
ABCDEFGHIJ
1RefColumn1Column2Column3Column4Column5Column6Column7Column8Column9
2XYZ10001234567OK
3XYZ10011234567OK
4XYZ10021234567OK
5XYZ10031234567OK
6XYZ10041234567OK
7XYZ10051234567OK
8XYZ10061234567OK
9XYZ10071234567OK
10XYZ10081234567OK
11XYZ10091234567OK
12XYZ10101234567OK
13XYZ10111234567OK
14XYZ10121234567OK
15XYZ10131234567OK
16XYZ10141234567OK
Sheet1
Cell Formulas
RangeFormula
A3:A16A3=A2+1
J2:J16J2=IF(OR(B2="",C2="",D2="",E2="",F2="",G2="",I1=""),"Missing Data","OK")
Cells with Conditional Formatting
CellConditionCell FormatStop If True
B2:G16,I2:I16Cell Value=""textNO


and when data is missing it looks like this:

Book1
ABCDEFGHIJ
1RefColumn1Column2Column3Column4Column5Column6Column7Column8Column9
2XYZ10001234567OK
3XYZ10011234567OK
4XYZ10021234567OK
5XYZ10031234567OK
6XYZ10041234567OK
7XYZ10051234567OK
8XYZ10061234567OK
9XYZ10071234567OK
10XYZ10081234567OK
11XYZ10091234567OK
12XYZ10101234567OK
13XYZ10111234567OK
14XYZ10121234567OK
15XYZ10131234567OK
16XYZ10141234567OK
Sheet1
Cell Formulas
RangeFormula
A3:A16A3=A2+1
J2:J16J2=IF(OR(B2="",C2="",D2="",E2="",F2="",G2="",I1=""),"Missing Data","OK")
Cells with Conditional Formatting
CellConditionCell FormatStop If True
B2:G16,I2:I16Cell Value=""textNO
 
Upvote 0
Hi Stuart,
Thanks a lot.
I really appreciate your work and response.
I tried the steps you provided but couldn't get the expected results may be I was deviating from the natural process. As I said I am new to VBA I don't have much idea so can you please elaborate the process.

Thanks in advance.

Regards,
Kiran
 
Upvote 0

Forum statistics

Threads
1,223,236
Messages
6,170,915
Members
452,366
Latest member
TePunaBloke

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top