Different values, different messages - advanced Data Validat

[inarbeth]

On a worksheet dates are to be inserted in cells C7 and C13. In C14 is a calculation to give C13 minus C7 provided both C7 and C13 contain dates and C13 is greater than C7 (i.e a number for date difference).
I already use data validation to control input in C7, C13 and C14. Users can also overwrite the formula in C14 in certain cases (the workbook is read-only so the formula revives when they re-open).

I want to activate different message boxes depending upon the date put in to C13 and upon the figure in C14. In addition rather like the warning feature in Data Validation users can ignore the messages so I only want them to appear when relevant cells are the active cell. In order of priority the messages I want are:
1. If the date in C13 is more than 91 but less than or equal to 181 days after today's date, Message 1 appears.
2. If the date in C13 is more than 181 days after today's date, Message 2 appears.

Messages 1 and 2 are to appear only if the entry has been direcly into C13 (NB I use a data validation dropdown box in C13 but the user can insert another date if required).

3. If the value in C14 is more than 91 but less than or equal to 181, message 3 appears.
4. If the value in C14 is more than 181, message 4 appears.

Messages 3 and 4 are to appear if the value of C14 is created either as a result of inputting dates in C7 or C13 or if the user overtypes C14 with their own figure. I think this means when any of C7, C13 or C14 is the ActiveCell.

The messages should not appear at other times. I have tried using Target.Address = C13 syntax but have got stuck and am on the point of shelling out for a book on VBA for Excel (probably a good investment but that's another story).

Can you also please include the code to change the title in the pop up message boxes to Title 1, Title 2 etc.

I have given cells C7, C13 and C14 names (let us call them Name1, Name2 and Name3 for the sake of example) and so would prefer to use these names rather than the cell references so that I can insert rows above 14 if necessary without having to re-do the code for the messages.
Many thanks in advance.

[inarbeth]

I can't offer deification unlike buntykins (Whoever answers this will be a god!!!!!!!)but can anyone help with this please? Will eternal gratitude be sufficient?

[Jay Petrulis]

In a sheet module (not a regular code module) try the following:

'-------------
Private Sub Worksheet_Change(ByVal Target As Excel.Range)

If Target.Address(False, False) = "C7" Then
If Target.Offset(7, 0) > 91 And _
Target.Offset(7, 0) <= 181 Then MsgBox "Three", , "Title3"
If Target.Offset(7, 0) > 181 Then MsgBox "Four", , "Title4"
End If

If Target.Address = "$C$13" Then
If Target.Value > Date + 91 And _
Target.Value <= Date + 181 Then MsgBox "One", , "Title1"
If Target.Value > Date + 181 Then MsgBox "Two", , "Title2"
End If

If Target.Address = "$C$14" Then
If Target.Value > 91 And _
Target.Value <= 181 Then MsgBox "Three", , "Title3"
If Target.Value > 181 Then MsgBox "Four", , "Title4"
End If

End Sub
'-----------------

Not elegant, and I'm not sure it meets your needs, but it may get you started.

Bye,
Jay

P.S. I am only in this for eternal gratitude!

[inarbeth]

Thanks Jay, I'll try this out. Eternal gratitude is yours!

[inarbeth]

Thanks Jay. I have been able to create the required messages in Excel 2000.
Two questions, if I may:
1. I have Excel 2000 at home and Excel 97 SR-2 in the office. When I use the Data Validation dropdown in C13 to insert a date more than 91 days away no message appears in Excel 97 but it does in Excel 2000. If I go back into C13 and edit the date in the formula bar in Excel 97 the macro works. Can anyone explain that?

2. Jay, is there a reason why you used Offset to tie in cells C7 and C14 as opposed to absolute references?

[Jay Petrulis]

Hi Ian,

I am unsure why this is not working for you. It was written with Excel 97, so it should work. Possibly it is working, but the triggers are not met. They may need to be reevaluated.

For question 2, either way will work. If you prefer the explicit declarations, change to Range("C14") and the like. They should work with no problem.

I will take another look at this, but it is working on my system.

Jay

EDIT: I didn't see the point where it works, but not on the Drop Down. I'll check that.

[ This Message was edited by: Jay Petrulis on 2002-04-30 09:51 ]

[inarbeth]

Thanks Jay. I have double-checked and confirm my results. I don't think it is relevant but in the office (Excel 97) we use a document management system called PDDOCS that interferes with some Excel functionality (but not so far as I am aware within a sheet).