# Excel for multiple users



## drvirus5528 (Dec 21, 2022)

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 (Dec 21, 2022)

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 (Dec 22, 2022)

Z51 said:


> 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.
> 
> ...



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 (Dec 22, 2022)

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 (Dec 22, 2022)

Z51 said:


> 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)
> ...



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 (Dec 22, 2022)

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


----------



## drvirus5528 (Dec 22, 2022)

Z51 said:


> 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 (Dec 22, 2022)

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 (Dec 22, 2022)

Z51 said:


> 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 (Dec 27, 2022)

drvirus5528 said:


> 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


----------

