show msg box if a cell is still blank after a date specified in another cell?

  • Thread starter Thread starter ****cameron****
  • Start date Start date
C

****cameron****

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.
 
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)
 
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...
 
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)
 
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...
 
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)
 
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
 
Back
Top