By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,896 Members | 1,997 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,896 IT Pros & Developers. It's quick & easy.

Checking date coming up soon?

P: 1
I have a spreadsheet where in Column M is a list of dates in the future. I would like to add a pop up message, so when I open the workbook, it lets me know if a date is within 30 days away.

Hope that makes sense!

Cheers
Apr 12 '12 #1
Share this Question
Share on Google+
1 Reply


Expert 100+
P: 634
Hi

Not sure what you want to do (ie just count the dates or list then), but with this code in the 'ThisWorkbook' module (and macros enabled!!) it is a start!?
Expand|Select|Wrap|Line Numbers
  1. Private Sub Workbook_Open()
  2.     Dim iDateCol As Integer
  3.     Dim iBotRow As Long
  4.     Dim i As Long
  5.     Dim DateCount As Long
  6.  
  7.     iDateCol = 13
  8.  
  9.  
  10.     ThisWorkbook.Sheets("Dates Sheet").Select
  11.  
  12.     ActiveCell.SpecialCells(xlLastCell).Select
  13.     iBotRow = ActiveCell.Row
  14.     Cells(1, 1).Activate
  15.  
  16.     For i = 1 To iBotRow
  17.         If IsDate(Cells(i, iDateCol)) Then
  18.             If Cells(i, iDateCol) <= Date + 30 Then DateCount = DateCount + 1
  19.         End If
  20.     Next i
  21.  
  22.     If DateCount > 0 Then MsgBox "There are " & DateCount & " dates within 30 dates in Coulmn 'M'.", vbInformation, "Due Dates"
  23.  
  24. End Sub
  25.  
You will have to change the name of the sheet containing the dates in line 10, just in caes there is more than one sheet.

MTB
Apr 13 '12 #2

Post your reply

Sign in to post your reply or Sign up for a free account.