Will Excel tell me when a date is near?

B

Blrmbabe

Hi all,
I was just wondering if Excel is capable of alerting its user when
date is near or when it has arrived. I know that alot of calande
programs will do this however those programs wont keep track of th
other information I need.

I am trying to keep a list of my buisness' clients and when thie
insurance expires. I would like a pop up message or some other type o
alert when a date comes near. I dont know if Excel can even do this.

Any suggestions will help.

Thank
 
B

Bob Phillips

Here's a technique that I discovered a few weeks ago, and I am getting fond
of.

This code assumes the client name in column A of Sheet1, and the date in
column B. It looks at every row and if the date is due within 10 days, it
adds it to a dialog sheet. The nice touch is that you can select from this
sheet, and do some further processing on the selected items.

Here's the code

Private Sub Workbook_Open()
Const nAgedDays As Long = 10
Dim cLastrow As Long
Dim nTopPos As Long
Dim iWarnings As Long
Dim i As Long
Dim PrintDlg As DialogSheet
Dim CurrentSheet As Worksheet
Dim cb As CheckBox

Application.ScreenUpdating = False

' Check for protected workbook
If ActiveWorkbook.ProtectStructure Then
MsgBox "Workbook is protected.", vbCritical
Exit Sub
End If

Set PrintDlg = ActiveWorkbook.DialogSheets.Add

With Worksheets("Sheet1")
cLastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
nTopPos = 40
For i = 1 To cLastrow
If .Cells(i, "B") < Date + nAgedDays Then
iWarnings = iWarnings + 1
PrintDlg.CheckBoxes.Add 78, nTopPos, 150, 16.5
PrintDlg.CheckBoxes(iWarnings).Text = _
.Cells(i, "A").Value & " - due on " & Format(.Cells(i,
"B").Value, "dd mmm yyyy")
nTopPos = nTopPos + 13
End If
Next i

PrintDlg.Buttons.Left = 240

With PrintDlg.DialogFrame
.Height = Application.Max _
(68, PrintDlg.DialogFrame.Top + nTopPos - 34)
.Width = 230
.Caption = "Select workbooks to process"
End With

PrintDlg.Buttons("Button 2").BringToFront
PrintDlg.Buttons("Button 3").BringToFront

.Activate

Application.ScreenUpdating = True
If PrintDlg.Show Then
For Each cb In PrintDlg.CheckBoxes
If cb.Value = xlOn Then
MsgBox Workbooks(cb.Caption).Name & " selected"
End If
Next cb
Else
MsgBox "Nothing selected"
End If

Application.DisplayAlerts = False
PrintDlg.Delete

End With

End Sub

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
M

Milez

You could program that in VBA, but prolly not worth it

Contact Management software is already available.
 
C

Charlie

One option is to set the Conditional format on the cells
containing the due dates to =TODAY()and format as red.
Then any time a date is due or past due the cells will be
in red.
 
S

Scottmk

Bob said:
*Here's a technique that I discovered a few weeks ago, and I a
getting fond
of.

This code assumes the client name in column A of Sheet1, and the dat
in
column B. It looks at every row and if the date is due within 1
days, it
adds it to a dialog sheet. The nice touch is that you can select fro
this
sheet, and do some further processing on the selected items.

Here's the code

Private Sub Workbook_Open()
Const nAgedDays As Long = 10
Dim cLastrow As Long
Dim nTopPos As Long
Dim iWarnings As Long
Dim i As Long
Dim PrintDlg As DialogSheet
Dim CurrentSheet As Worksheet
Dim cb As CheckBox

Application.ScreenUpdating = False

' Check for protected workbook
If ActiveWorkbook.ProtectStructure Then
MsgBox "Workbook is protected.", vbCritical
Exit Sub
End If

Set PrintDlg = ActiveWorkbook.DialogSheets.Add

With Worksheets("Sheet1")
cLastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
nTopPos = 40
For i = 1 To cLastrow
If .Cells(i, "B") < Date + nAgedDays Then
iWarnings = iWarnings + 1
PrintDlg.CheckBoxes.Add 78, nTopPos, 150, 16.5
PrintDlg.CheckBoxes(iWarnings).Text = _
.Cells(i, "A").Value & " - due on " & Format(.Cells(i,
"B").Value, "dd mmm yyyy")
nTopPos = nTopPos + 13
End If
Next i

PrintDlg.Buttons.Left = 240

With PrintDlg.DialogFrame
.Height = Application.Max _
(68, PrintDlg.DialogFrame.Top + nTopPos - 34)
.Width = 230
.Caption = "Select workbooks to process"
End With

PrintDlg.Buttons("Button 2").BringToFront
PrintDlg.Buttons("Button 3").BringToFront

.Activate

Application.ScreenUpdating = True
If PrintDlg.Show Then
For Each cb In PrintDlg.CheckBoxes
If cb.Value = xlOn Then
MsgBox Workbooks(cb.Caption).Name & " selected"
End If
Next cb
Else
MsgBox "Nothing selected"
End If

Application.DisplayAlerts = False
PrintDlg.Delete

End With

End Sub

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

message
Click to expand...

Hey Bob,
What exactly does that code do? e.g. I'm not really sure what you mea
by adding it to a dialog sheet. Any chance that you can get it to sen
an email alert?
Thank
 
S

Scottmk

BTW,
It looks like I am getting an error on this part of the code:

PrintDlg.CheckBoxes(iWarnings).Text = _
.Cells(i, "A").Value & " - due on " & Format(.Cells(i,
"B").Value, "dd mm yyyy")

Thanks,
Scot
 
B

Bob Phillips

Scott,

That is because you are getting wrap-around. The 2nd and 3rd line in this
part that you show should all be on one line.

As to what the code does, it is all explained in my original post,
identifying dates earlier than 10 days forward. The dialog sheet is created
in the code, and every qualifying item is added to that dialog with a
checkbox. The idea was that where I checked which checkbox was set and
output a MsgBox the OP would add their own code to process as required.

If you have the same circumstances, clients in column A, dates in column B,
and you want to pick out any earlier than 10- days from now, and select from
that list and email to them, yes it can be done, as long as you have the
email address. If this is what you want, try the code amended as I have
suggested, and post back for the changes that you want.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
S

Scottmk

Hey Bob,
Thanks. All of the circumstances are the same. I fixed the code, bu
either I don't know how to run it or I don't know where the dialog bo
is. Any ideas? I looked for it under my macros and there was nothin
there. I tried putting it in both the worksheet and its own module
 
B

Bob Phillips

Scott,

The code should go in the ThisWorkbook code module.

If you want, you could send me your workbook, and I will try and sort it.
Send it to
Bob<dot>Phillips<at>tiscali<dot>.co<dot>uk

change the <dot> to . and teh <at> to @

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Top