Mandatory Cells and knowing to fill in the next row!

[I heart Excel]

Hello,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
I am very stuck on something I have been looking at for a while.<o></o>
<o></o>
I have a spreadsheet for adding new items to our internal system, with numerous headers.<o></o>
Some of the fields (but not all) are mandatory.<o></o>
This is filled out and then saved by our users, and then checked daily by me.<o></o>
<o></o>
I would like to know if there is anyway of creating a macro or something to make the fields I require mandatory? <o></o>
I would then like this macro to know that it is to only run on the next blank row. <o></o>
<o></o>
Eventually I would like my users to enter the items they require, fill out all the mandatory fields before being able to save, or they get an error message.<o></o>
When the next person accesses the form to enter their data underneath, it will again, not allow them to save until all the mandatory fields are filled out.<o></o>

Help will be much appreciated,

Thanks in advance

 

[lenze]

Hi and welcome to the board!!!
A few assumptions for coding purposes. You will need to edit for your needs.
1) Sheet1 is the sheet of interest!
2) There is data in Column "A".
3) You have 6 Columns of data.

Place this in the ThisWorkBook module

Public LR As Long
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
If Application.WorksheetFunction.CountA(Sheet1.Cells(LR + 1, "A").Resize(1, 6)) <> 6 Then
   rspn = MsgBox("You have missed one or more required fields!!" & Chr(10) & "Do you wish to save anyway?", vbYesNo)
   If rspn = vbNo Then Cancel = True
End If
End Sub
Private Sub Workbook_Open()
LR = Sheet1.Cells(Rows.Count, "A").End(xlUp).Row
MsgBox LR
End Sub

Depending on your layout, you may need to make adjustments using a Union of your cells instead of the Resize property!!
HTH
lenze

 

[I heart Excel]

Worked like a charm,

thank you very much for your help