PDA

View Full Version : [SOLVED] Macro to check dates and notify user



Hobb3s
07-07-2005, 12:17 PM
Hello,
I was wondering if it is possible to run a macro when the user opens the spreadsheet that Checks all the dates in A1:A50. Then if the date is not todays date or further into the future then it warns the user/prompts for the date to be changed.
Any help is much appreciated.
Thank you,
Greg.

Jacob Hilderbrand
07-07-2005, 12:34 PM
Something like this in the Workbook Open event should get you started.


Dim i As Long
For i = 1 to 50
If Sheets("Sheet1").Range("A" & i).Value < Date Then
MsgBox "Change the date."
Exit For
End If
Next i

Hobb3s
07-08-2005, 05:24 AM
That's a great start, thanks! How do I make it so that it ignores cells that do not yet have any values?

Bob Phillips
07-08-2005, 05:39 AM
That's a great start, thanks! How do I make it so that it ignores cells that do not yet have any values?


Dim i As Long
For i = 1 To 50
With Sheets("Sheet1").Range("A" & i)
If .Value <> "" Then
If .Value < Date Then
MsgBox "Change the date."
Exit For
End If
End If
End With
Next i

Hobb3s
07-08-2005, 05:53 AM
Thanks for the quick response, I just figured it out this second..


Private Sub Workbook_Open()
Dim i As Long
For i = 4 To 54
If Sheets("Sales").Range("H" & i).Value < Date And Sheets("Sales").Range("H" & i).Value <> 0 Then
MsgBox "Fix the date please"
Exit For
End If
Next i
End Sub

It seems to work that way.. I really need my work to get me a vba in excel for dummies book or something to help me with the simple stuff.
Thanks guys!

Hobb3s
07-08-2005, 06:02 AM
One more quick question.. how do I return the name and/or value of the cell that is the problem... maybe highlight it on the sheet as well.

Hobb3s
07-08-2005, 06:42 AM
ok, I figured it out.. found an online resource that explains some of the simpler stuff.. like syntax. Appreicate all the help.

Bob Phillips
07-08-2005, 06:44 AM
One more quick question.. how do I return the name and/or value of the cell that is the problem... maybe highlight it on the sheet as well.



With Sheets("Sheet1").Range("A1:A50")
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, _
Formula1:="=AND(A1<>"""",A1<TODAY())"
.FormatConditions(1).Interior.ColorIndex = 38
End With

excelliot
07-09-2005, 02:11 AM
No more issues?