PDA

View Full Version : Solved: POP UP MESSAGE



BENSON
04-18-2007, 11:47 PM
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
04-19-2007, 12:08 AM
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
04-19-2007, 01:05 AM
Charlize the dates are already present

THKS

Bob Phillips
04-19-2007, 01:22 AM
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
04-19-2007, 12:11 PM
You could also use data validation

BENSON
04-19-2007, 10:38 PM
What would the custom code be for data valadation to obtain a pop up message

Thks

Bob Phillips
04-20-2007, 12:29 AM
DV wouldn't name the holiday, it could only tell you as you enter a date that it is a holiday.

Charlize
04-20-2007, 04:35 AM
Try this. Lookup dates are on sheet2. A = date and B = description.
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 SubCharlize

BENSON
04-20-2007, 05:10 AM
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
04-20-2007, 05:44 AM
This one ?
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 SubCharlize

Charlize
04-20-2007, 05:52 AM
... One thing extra if you could,:confused: 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
04-20-2007, 06:01 AM
THANK YOU SO MUCH ,YOU GUYS ARE GREAT