Alert message on saving the document needed

Pauljj

Well-known Member
Joined
Mar 28, 2004
Messages
2,013
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
CDEFGH
2
3SunMon
4NameStaffNoPatterNo03Jan04Jan
5RossHarris#1XLEXLE
6PaulAcheson#31X1
7PaulineNewman#11X1
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 ??
 

Some videos you may like

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

Pauljj

Well-known Member
Joined
Mar 28, 2004
Messages
2,013
This will be from EC5:FO50


Dates will be from G4:AS4

Names will be from C5:C50


Cheers
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
Try this in the ThisWorkbook module:

Code:
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

Well-known Member
Joined
Mar 28, 2004
Messages
2,013
Legendary, thank you very much, I will analyse and work out how it does the job

Many thanks
 

Watch MrExcel Video

Forum statistics

Threads
1,122,657
Messages
5,597,391
Members
414,142
Latest member
Banyangt

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top