Alert message on saving the document needed

[Pauljj]

I want users to be aware of an error at the point they save the document

This will be triggered by a code appearing. For example if there is a problem in cell G5 then ERR1 or ERR2 or ERR3 will appear in EC5, if the problem was in G6 then the code would appear in EC6, if it was in H6 the code would appear in ED6 and so on...hopefully thats straightforward

As you can see below..............

Bournemouth Roster 2010.xls
	C	D	E	F	G	H
2
3					Sun	Mon
4	Name		StaffNo	PatterNo	03Jan	04Jan
5	RossHarris		#	1	XLE	XLE
6	PaulAcheson		#	31	X	1
7	PaulineNewman		#	11	X	1
January

If the problem was with G5, an error code would be in EC5. I would need an alert warning to come up and say there is an error with Ross Harris (all names in column C) on the 03 Jan (all dates on row 4

Like wise if the problem was in I7 an error code (which will always start with ERR) will appear on EE7 and the warning message will say there is an error with Pauline Newman on the 05 Jan

Does that all makes sense, it all follows a pattern, for me its just getting it to look at Column C and Row 4 for the additional details to appear in the warning message...any ideas ??

 

[Andrew Poulsom]

So exactly what range needs to be checked for ERR?

 

[Pauljj]

This will be from EC5:FO50


Dates will be from G4:AS4

Names will be from C5:C50


Cheers

 

[Andrew Poulsom]

Try this in the ThisWorkbook module:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Dim Sh As Worksheet
    Dim Cell As Range
    Set Sh = Worksheets("Sheet1")
    For Each Cell In Sh.Range("EC5:FO50")
        If Left(Cell.Value, 3) = "ERR" Then
            MsgBox "Error for " & Cell.EntireRow.Cells(1, 3).Value _
                & " on " & Sh.Rows(4).Cells(1, Cell.Column - 126).Value
            Cancel = True
            Exit Sub
        End If
    Next Cell
End Sub

 

[Pauljj]

Legendary, thank you very much, I will analyse and work out how it does the job

Many thanks