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

[Kiran2229Kiran]

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

 

[StuLux]

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

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
	A	B	C	D	E	F	G	H	I	J
1	Ref	Column1	Column2	Column3	Column4	Column5	Column6	Column7	Column8	Column9
2	XYZ1000	1	2	3	4	5	6		7	OK
3	XYZ1001	1	2	3	4	5	6		7	OK
4	XYZ1002	1	2	3	4	5	6		7	OK
5	XYZ1003	1	2	3	4	5	6		7	OK
6	XYZ1004	1	2	3	4	5	6		7	OK
7	XYZ1005	1	2	3	4	5	6		7	OK
8	XYZ1006	1	2	3	4	5	6		7	OK
9	XYZ1007	1	2	3	4	5	6		7	OK
10	XYZ1008	1	2	3	4	5	6		7	OK
11	XYZ1009	1	2	3	4	5	6		7	OK
12	XYZ1010	1	2	3	4	5	6		7	OK
13	XYZ1011	1	2	3	4	5	6		7	OK
14	XYZ1012	1	2	3	4	5	6		7	OK
15	XYZ1013	1	2	3	4	5	6		7	OK
16	XYZ1014	1	2	3	4	5	6		7	OK
Sheet1

Cell Formulas
Range	Formula
A3:A16	A3	=A2+1
J2:J16	J2	=IF(OR(B2="",C2="",D2="",E2="",F2="",G2="",I1=""),"Missing Data","OK")

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B2:G16,I2:I16	Cell Value	=""	text	NO

and when data is missing it looks like this:

Book1
	A	B	C	D	E	F	G	H	I	J
1	Ref	Column1	Column2	Column3	Column4	Column5	Column6	Column7	Column8	Column9
2	XYZ1000	1	2	3	4	5	6		7	OK
3	XYZ1001	1	2	3	4	5	6		7	OK
4	XYZ1002	1	2	3	4	5	6		7	OK
5	XYZ1003	1	2	3	4	5	6		7	OK
6	XYZ1004	1	2	3	4	5	6		7	OK
7	XYZ1005	1	2	3	4	5	6		7	OK
8	XYZ1006	1	2	3	4	5	6		7	OK
9	XYZ1007	1	2	3	4	5	6		7	OK
10	XYZ1008	1	2	3	4	5	6		7	OK
11	XYZ1009	1	2	3	4	5	6		7	OK
12	XYZ1010	1	2	3	4	5	6		7	OK
13	XYZ1011	1	2	3	4	5	6		7	OK
14	XYZ1012	1	2	3	4	5	6		7	OK
15	XYZ1013	1	2	3	4	5	6		7	OK
16	XYZ1014	1	2	3	4	5	6		7	OK
Sheet1

Cell Formulas
Range	Formula
A3:A16	A3	=A2+1
J2:J16	J2	=IF(OR(B2="",C2="",D2="",E2="",F2="",G2="",I1=""),"Missing Data","OK")

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B2:G16,I2:I16	Cell Value	=""	text	NO

 

[Kiran2229Kiran]

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

 

[StuLux]

What problems did you encounter?