Excel VB script to notify/popup Solved/Closed

Question

Hello, 
I want to have different cells in the worksheet have pop up messages/notifications when the date or deadline is reached. so when the 1 june 2011 arrives there is a pop with message to remind on work that should have been received for an example.

Your assistance will greatly appreciated


Configuration: Windows Vista / Internet Explorer 8.0

dimi · Answer

anyone who can assist with the code I would appreciate it.

something close to:
Private Sub Workbook_Open()
   Dim cl As Range
   Set cl = ThisWorkbook.Sheets("Sheet1").Range("A1")
   If IsDate(cl) Then
       If Now >= cl Then
           MsgBox "The date in Sheet1 cell A1 has been reached or passed."
       End If
   End If
End Sub


except it needs be assigned to each cell, as each cell in worksheet has different deadline dates. The above code is for the whole workbook which doesn''t meet the requirements. Thank you

rizvisa1 · Answer

First I dont think that it is a very good idea to loop thru each cell. If you have few thousand cells, it will take some time to scan thru each cell.  

However if you still want to scan thru each cell you can use some thing like this 

Private Sub Workbook_Open() 
   Dim cl               As Range
   Dim lMaxRows         As Long 
   Dim lMaxCols         As Long 
    
   With Sheets("Sheet1") 
      lMaxRows = getItemLocation("*", .Cells) 
      If lMaxRows = 0 Then Exit Sub 
      lMaxCols = getItemLocation("*", .Cells, bFindRow:=False) 
      For Each cl In .Range(.Cells(1, 1), .Cells(lMaxRows, lMaxCols)) 
         If IsDate(cl) Then 
            If Now >= cl Then 
               MsgBox "The date in " & .Name & ", cell " & cl.Address(False, False) & " has been reached or passed." 
            End If 
         End If 
      Next 
   End With 
End Sub 


Public Function getItemLocation(sLookFor As String, _ 
                                rngSearch As Range, _ 
                                Optional bFullString As Boolean = True, _ 
                                Optional bLastOccurance As Boolean = True, _ 
                                Optional bFindRow As Boolean = True) As Long 
     
   Dim Cell             As Range 
   Dim iLookAt          As Integer 
   Dim iSearchDir       As Integer 
   Dim iSearchOdr       As Integer 
     
   If (bFullString) _ 
   Then 
      iLookAt = xlWhole 
   Else 
      iLookAt = xlPart 
   End If 
   If (bLastOccurance) _ 
   Then 
      iSearchDir = xlPrevious 
   Else 
      iSearchDir = xlNext 
   End If 
   If Not (bFindRow) _ 
   Then 
      iSearchOdr = xlByColumns 
   Else 
      iSearchOdr = xlByRows 
   End If 
     
   With rngSearch 
      If (bLastOccurance) _ 
      Then 
         Set Cell = .Find(sLookFor, .Cells(1, 1), xlValues, iLookAt, iSearchOdr, iSearchDir) 
      Else 
         Set Cell = .Find(sLookFor, .Cells(.Rows.Count, .Columns.Count), xlValues, iLookAt, iSearchOdr, iSearchDir) 
      End If 
   End With 
     
   If Cell Is Nothing Then 
      getItemLocation = 0 
   ElseIf Not (bFindRow) _ 
   Then 
      getItemLocation = Cell.Column 
   Else 
      getItemLocation = Cell.Row 
   End If 
   Set Cell = Nothing 

End Function

Excel VB script to notify/popup

2 responses

Similar discussions

Subscribe To Our Newsletter!