Excel for multiple users

[drvirus5528]

I have an excel file that will be used by multiple users to collect data for months but one specific column is always left empty and I want to make that column mandatory.
I tried but I could only make one cell mandatory and the rest would not respond and the next try, I could make a couple of cells mandatory but if I fill only one row, the empty cells not populated does not allow me to save.

How can I get this column to be mandatory, be able to be saved and closed by a user for another user to pick up and populate as well?

Please any idea will be great.

 

[Z51]

In the Workbook's Before_Save event, you could have code checking the values in that column. I assume you're not wanting to check all 1 million+ rows in that column, and that there will be data in a different column which will then require a value to be entered into your required column.

For this example, I've assumed the required column is D, and that you want a value in that column any time there is a value in column A. The code finds the last used row in column A, then scans through column A checking to see that it has a value and that column D does not have a value. If so, after scanning the used range, it then puts up a message saying which cells in column D are required and then cancels the Save process. If no exceptions are found, it proceeds to Save the file.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim ce As Range, lastrow As Long, str As String
lastrow = Range("A" & Rows.Count).End(xlUp).Row

For Each ce In Range("A2:A" & lastrow)
    If ce.Value <> "" And ce.Offset(0, 3).Value = "" Then
        str = str & ", " & ce.Offset(0, 3).Address
    End If
Next ce

If str <> "" Then
    Cancel = True
    MsgBox "A value is required in cell(s) " & Mid(str, 3, Len(str)) & " before saving this file."
End If
End Sub

 

[drvirus5528]

In the Workbook's Before_Save event, you could have code checking the values in that column. I assume you're not wanting to check all 1 million+ rows in that column, and that there will be data in a different column which will then require a value to be entered into your required column.

For this example, I've assumed the required column is D, and that you want a value in that column any time there is a value in column A. The code finds the last used row in column A, then scans through column A checking to see that it has a value and that column D does not have a value. If so, after scanning the used range, it then puts up a message saying which cells in column D are required and then cancels the Save process. If no exceptions are found, it proceeds to Save the file.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim ce As Range, lastrow As Long, str As String
lastrow = Range("A" & Rows.Count).End(xlUp).Row

For Each ce In Range("A2:A" & lastrow)
    If ce.Value <> "" And ce.Offset(0, 3).Value = "" Then
        str = str & ", " & ce.Offset(0, 3).Address
    End If
Next ce

If str <> "" Then
    Cancel = True
    MsgBox "A value is required in cell(s) " & Mid(str, 3, Len(str)) & " before saving this file."
End If
End Sub

Thank you very much in this case, I have column E to make mandatory. Row 3 is where input begins. I am attaching an image of the exact sheet here for reference.

 

[Z51]

So do you know what to modify in the code to suit it to your needs? If not, then make these adjustments to three lines:

For Each ce In Range("A3:A" & lastrow)
    If ce.Value <> "" And ce.Offset(0, 4).Value = "" Then
        str = str & ", " & ce.Offset(0, 4).Address

Again, this assumes that you only want to verify there is a value in column E **if a value exists in column A**. If you want to verify against a different column, adjust the range references from "A" to "B" or "C", etc., and then change the offset value from 4 to the # of columns away from column E (positive or negative).

 

[drvirus5528]

So do you know what to modify in the code to suit it to your needs? If not, then make these adjustments to three lines:

For Each ce In Range("A3:A" & lastrow)
    If ce.Value <> "" And ce.Offset(0, 4).Value = "" Then
        str = str & ", " & ce.Offset(0, 4).Address

Again, this assumes that you only want to verify there is a value in column E **if a value exists in column A**. If you want to verify against a different column, adjust the range references from "A" to "B" or "C", etc., and then change the offset value from 4 to the # of columns away from column E (positive or negative).

So honestly I am super new to excel so I can use your help in knowing where to change what to suit my needs.
please help me with the complete code referencing from the image I posted earlier and I will test and let you know.

 

[Z51]

I already posted the lines you'll need to change based on my assumptions.

 

[drvirus5528]

I already posted the lines you'll need to change based on my assumptions.

Okay . I suck at codes so I will read through over and over to understand and apply. ... Thank you and if I get any other question I will ask.

 

[Z51]

To start in row 3, you just need to change "A2:A" to "A3:A".

To reference column E instead of column D, you just need to change the two references of "ce.Offset(0,3)" to "ce.Offset(0,4)". (Column D is 3 columns to the right of Column A, while Column E is 4 columns to the right.)

 

[drvirus5528]

To start in row 3, you just need to change "A2:A" to "A3:A".

To reference column E instead of column D, you just need to change the two references of "ce.Offset(0,3)" to "ce.Offset(0,4)". (Column D is 3 columns to the right of Column A, while Column E is 4 columns to the right.)

I cant be grateful enough.. That is so much better of an explanation. I will update the community when I test is all out... Thank you

 

[drvirus5528]

I cant be grateful enough.. That is so much better of an explanation. I will update the community when I test is all out... Thank you

Thank you so very much... It worked