Solved: POP UP MESSAGE

[BENSON]

Could some one please assist me on creating a pop up message. If a collum of cells ( A5:A70) has certain dates in them ,and one of the dates coincides with a public holiday I would like a pop up message to appear naming the specific public holiday if this could be done I would be most thankful.

[Charlize]

Are those dates already present are must they be checked when entered in your range A5:A70 ?

- if not present use worksheet change and check target.value
- check target.value against a list of dates in A and comments in B on sheet2
- if found, msgbox and remove contents of target

Something like this ?

Charlize

[BENSON]

Charlize the dates are already present

THKS

[Bob Phillips]

If you have a table of holidays, dates and names, you can use MATCH and VLOOKUP in code to check the date list against that table.

[johnske]

You could also use data validation

[BENSON]

What would the custom code be for data valadation to obtain a pop up message

Thks

[Bob Phillips]

DV wouldn't name the holiday, it could only tell you as you enter a date that it is a holiday.

[Charlize]

Try this. Lookup dates are on sheet2. A = date and B = description.
[vba]Sub Get_Dates()
Dim Cell As Range
Dim Search_date As String
Dim V_message As String
If ActiveCell.Row < 5 Or ActiveCell.Column > 1 Then Exit Sub
If ActiveCell.Value = vbNullString Then Exit Sub
Search_date = Worksheets("Sheet1").Range(ActiveCell.Address).Value
For Each Cell In Worksheets("Sheet2").Range("A2:A" & _
Worksheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Row)
If Cell Like "*" & Search_date & "*" Then
V_message = Search_date & " - " & Cell.Offset(0, 1).Value
Exit For
End If
Next Cell
If V_message = vbNullString Then
MsgBox Search_date & " isn't a holiday !", vbCritical, "Watch out !!!"
Else
MsgBox V_message, vbInformation, "Requested info ..."
End If
End Sub[/vba]Charlize

[BENSON]

Thanks for the code Charlize, its very cool .One thing extra if you could,what if there is a number of public holidays in the list could the pop up message indicate more than one ?

Thanks

[Charlize]

This one ?
[VBA]Sub Get_Dates()
Dim Cell As Range
Dim Cell1 As Range
Dim V_message As String
For Each Cell1 In Worksheets("Sheet1").Range("A5:A" & _
Worksheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row)
'sheet 2 starts at row 2 with dates in column A
'and description in column B
For Each Cell In Worksheets("Sheet2").Range("A2:A" & _
Worksheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Row)
If Cell Like "*" & Cell1.Value & "*" Then
V_message = V_message & Cell1 & " - " & Cell.Offset(0, 1).Value & vbCrLf
Exit For
End If
Next Cell
Next Cell1
If V_message = vbNullString Then
MsgBox "No holidays in list !", vbInformation, "Holiday check."
Else
MsgBox V_message, vbCritical, "Holiday check"
End If
End Sub[/VBA]Charlize

[Charlize]

... One thing extra if you could,

if I could ... you can do that to. Just a matter of really want to be able to do this kind of stuff.

Charlize

[BENSON]

THANK YOU SO MUCH ,YOU GUYS ARE GREAT