Hi All,
I am a relatively new user to vba but i have been thrust with with the
task of a spreadsheet analysing data from a sales team. Anyway, my
problem is that I have a worksheet where one column is the date that
customer made contact and another where date is entered when sale was
completed. I would like to have a msg box display asking sales rep on
status of sale if the sale completed date is still blank after a st
period of time from the initial contact column date. I am sure this is
possible I just don't know how. Any help would be appreciated.
Regards,
Cameron.


---
Message posted from http://www.ExcelForum.com/

Cameron,

Problem here is what will trigger the msgbox, and what happens if many are
due?

an alternative is to use conditional formatting to highlight all of those
overdue.

--

HTH

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

"****cameron**** " wrote in message
...
Hi All,
I am a relatively new user to vba but i have been thrust with with the
task of a spreadsheet analysing data from a sales team. Anyway, my
problem is that I have a worksheet where one column is the date that
customer made contact and another where date is entered when sale was
completed. I would like to have a msg box display asking sales rep on
status of sale if the sale completed date is still blank after a st
period of time from the initial contact column date. I am sure this is
possible I just don't know how. Any help would be appreciated.
Regards,
Cameron.


---
Message posted from http://www.ExcelForum.com/

Thanks for the reply bob,
I had thought of conditional formatting but there can be upwards of
50-60 entries a day and I feel that the users won't scroll up to look
at the entries as the reminders need to set in 3-4 day intervals. I
should have clarified better. What I hope to do is something similar
to outlooks' reminder function for it's calender where reminder's
cascade according to oldest to earliest. Not sure if this can be done
though...


---
Message posted from http://www.ExcelForum.com/

Cameron,

It can be done, but it needs a trigger event, such as opening the workbook,
and rules on how multiples will be handled (one message at a time or a
composite, as an example).

--

HTH

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

"****cameron**** " wrote in message
...
Thanks for the reply bob,
I had thought of conditional formatting but there can be upwards of
50-60 entries a day and I feel that the users won't scroll up to look
at the entries as the reminders need to set in 3-4 day intervals. I
should have clarified better. What I hope to do is something similar
to outlooks' reminder function for it's calender where reminder's
cascade according to oldest to earliest. Not sure if this can be done
though...


---
Message posted from http://www.ExcelForum.com/

thanks again Bob,
Yeah I want to have them trigger when the workbook opens and have eac
case have a reminder open in order of oldest tp latest. I am startin
to think these guys aren't paying me enough...

--
Message posted from http://www.ExcelForum.com

Cameron,

Here's one way to do it
#Option Explicit

Private Sub Workbook_Open()
Const nAgedDays As Long = 30
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

' Add a temporary dialog sheet
Set PrintDlg = ActiveWorkbook.DialogSheets.Add


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

' Move the OK and Cancel buttons
PrintDlg.Buttons.Left = 240

' Set dialog height, width, and caption
With PrintDlg.DialogFrame
.Height = Application.Max _
(68, PrintDlg.DialogFrame.Top + nTopPos - 34)
.Width = 230
.Caption = "Select workbooks to process"
End With

' Change tab order of OK and Cancel buttons
' so the 1st option button will have the focus
PrintDlg.Buttons("Button 2").BringToFront
PrintDlg.Buttons("Button 3").BringToFront

.Activate

' Display the dialog box
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

' Delete temporary dialog sheet (without a warning)
Application.DisplayAlerts = False
PrintDlg.Delete

' Reactivate original sheet

End With

End Sub




--

HTH

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

"****cameron**** " wrote in message
...
thanks again Bob,
Yeah I want to have them trigger when the workbook opens and have each
case have a reminder open in order of oldest tp latest. I am starting
to think these guys aren't paying me enough....


---
Message posted from http://www.ExcelForum.com/

thanks alot bob you rock. I will give this a go. I appreciate your
interest in my little quandrey...If you ever want to know amazing cost
effective ways to collect social and market research data, look me up.
cheers again


---
Message posted from http://www.ExcelForum.com/