Hi Alex

Try again. The code check that required cells in all sheets contains data
before the user can save the workbook.

This code will also work if the user try to use "save as" or if he want to
close the workbook and click yes, beeing asked to save the workbook before
close.

Btw: You can select all the cells in a sheet and then name the selected
cells. Enter the name in the field left to the formula line. Then you can
use this name in a macro like this:

Range("StarterArea").Select


Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _
Cancel As Boolean)

' this will check that all green areas are filled in

Dim Msg As String
Dim checkCells As Range
Dim c As Range
Dim cCount As Single

Set checkCells = Sheets("Amendment").Range("c6,k6,c7,k7,d45")
For Each c In checkCells
If IsEmpty(c) = True Then Cancel = True
Next
If Cancel = True Then
Sheets("Amendment").Select
Msg = MsgBox _
("Attention ALL GREEN AREAS must be filled in prior to saving. PLease go
back and check your work", _
vbOKOnly, "ATTENTION REQUIRED!!")
cCount = cCount + 1
End If

Cancel = False
Set checkCells = _
Sheets("Starter").Range _
("c6,k6,c7,k7,c11,c14,c15,c16,c17,c18,c20,e20,e21, e22,a26,b26,c26,d26,a30,k29,k30,l30,n30,o30,q30,r3 0,k31,l31,m31,n31,o31,p31,q31,r31,d45")
For Each c In checkCells
If IsEmpty(c) = True Then Cancel = True
Next
If Cancel = True Then
Sheets("Starter").Select
Msg = MsgBox("Attention ALL GREEN AREAS must be filled in prior
tosaving. PLease go back and check your work", vbOKOnly, "ATTENTION
REQUIRED!!")
cCount = cCount + 1
End If

Cancel = False
Set checkCells = _
Sheets("leaver").Range("c6,k6,c7,k7,c37,l37,c38,d4 1,n41,d45")
For Each c In checkCells
If IsEmpty(c) = True Then Cancel = True
Next
If Cancel = True Then
Sheets("Leaver").Select
Msg = MsgBox("Attention ALL GREEN AREAS must be filled in prior to
saving. PLease go back and check your work", vbOKOnly, "ATTENTION
REQUIRED!!")
cCount = cCount + 1
End If

If cCount 0 Then Cancel = True
End Sub

Regards,
Per


skrev i en meddelelse
...
On 1 Jan, 16:22, "Per Jessen" wrote:
Hi Alex
Considering the comments from Dave your own code can work like this.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _
Cancel As Boolean)

' this will check that all green areas are filled in

Dim Msg As String
Dim checkCells As Range
Dim c As Range

Select Case ActiveSheet.Name
Case "Amendment"
Set checkCells = Sheets("Amendment").Range("c6,k6,c7,k7,d45")
For Each c In checkCells
If IsEmpty(c) = True Then Cancel = True
Next
If Cancel = True Then
Msg = MsgBox("Attention ALL GREEN AREAS must be filled in prior to
saving. PLease go back and check your work", vbOKOnly, "ATTENTION
REQUIRED!!")
End If
Case "Starter"
Set checkCells = _
Sheets("Starter").Range("c6,k6,c7,k7,c11,c14,c15,c 16,c17,c18,c20,e20,e21,e2*2,a26,b26,c26,d26,a30,k2 9,k30,l30,n30,o30,q30,r30,k31,l31,m31,n31,o31,p31, q*31,r31,d45")
For Each c In checkCells
If IsEmpty(c) = True Then Cancel = True
Next
If Cancel = True Then
Msg = MsgBox("Attention ALL GREEN AREAS must be filled in prior to
saving. PLease go back and check your work", vbOKOnly, "ATTENTION
REQUIRED!!")
End If
Case "Leaver"
Set checkCells =
Sheets("leaver").Range("c6,k6,c7,k7,c37,l37,c38,d4 1,n41,d45")
For Each c In checkCells
If IsEmpty(c) = True Then Cancel = True
Next
If Cancel = True Then
Msg = MsgBox("Attention ALL GREEN AREAS must be filled in prior to
saving. PLease go back and check your work", vbOKOnly, "ATTENTION
REQUIRED!!")
End If
End Select
End Sub

Regards,
Per
-

Thanks it works fine, and I have wondered if it is possible for the
user who does not fill the mandatory areas in but tries to close the
workbook to stop the save and in fact not give the option to save as
either.

many thanks

Alex