Macro or VBA warning box on opening Excel

[Bisbee]

I've created an Excel spreadsheet with various data (a checklist of sorts). One column in the spreadsheet will have a date that a contract needs to be returned to our office. I would like to be able to have a message box alert us to the fact that a contract is not back within the specified time the column states. This return date column will be in several worksheets as well (each worksheet represents a month). I would especially like this box to appear as I'm opening Excel. I know I can put a simple formula in the cell that will do the trick, but I do not want to scroll down the column looking for this message as the column could be long and on several sheets. Am I asking too much from Excel? Unfortunately, I'm not familiar with VBA to be able to write a fomula - any help out there?

 

[euskadi]

Here's a way to tackle your project...

Put this code in the sheet module of each of your monthly worksheets. Then name the range that contains the due date "due_date_range". When you activate the sheet this will look to see if you've visited the worksheet yet today, and if you haven't it will look to see how many contracts you have which are past due, and it will give you a message.

Private Sub Worksheet_Activate()
    Dim pastcount As Long
    thisdate = ActiveSheet.Range("A1").Value
    
    If thisdate <> Date Then
        pastcount = 0
        
        For Each cell In ActiveSheet.Range(&quot;due_date_range&quot;)
            celldate = cell.Value
            If celldate < Date Then
                pastcount = pastcount + 1
            End If
        Next cell
        
        If pastcount > 0 Then
            MsgBox &quot;This worksheet has  &quot; & pastcount & &quot; contract(s) past due.&quot;
        End If
        
        ActiveSheet.Range(&quot;A1&quot;).Value = Date
    End If
End Sub

I recommend that you combine this with conditional formating that will change all due dates big, bold, and red if they are over due so you can quickly find them.

Of course, this assumes you have multiple contracts per sheet. If you only have one contract date per worksheet then I'd recommend keeping it simple and using conditional formatting to flag a worksheet that has a past-due date.

 

[Bisbee]

euskadi - First, thanks for the instruction - I'm further ahead today than I was yesterday. However, I'm still stuck. I'm getting an "EndIf without Block if" message. I've tried any number of things to figure it out - but can't. Any hints?

 

[euskadi]

You've got an extra for or next or with or loop or end if in there some where. Can you post what you've got so we can take a look?

 

[Bisbee]

Originally, I printed the VBA from this site. I tried coping and pasting the VBA into Word. Low and behold, there was one important line that was left out: Next Cell. All is fixed and running perfectly. - Thanks!