Set a Reminder

[R1chard]

We have a spreadsheet, which contains a list of cancellations. They all have a date on which they need to be cancelled.

There is not often a cancellation so the spreadsheet is not checked everyday.

What I wanted was a message to pop up on my desktop to remind me that I have a cancellation today on my excel spreadsheet.

If the cancellation date in column C, matches today’s date, (if the spreadsheet is not open) I would like a message to pop up on my desktop to tell me that there is a cancellation due on my spreadsheet.

I have doubts whether this can be done, but you never know.

Hope you can help! :wink:

 

[TommyGun]

That's strange...I just copied it to a text file, and it compiled and ran successfully. Maybe something was changed when you copied it. Can you save the VBS file as a .TXT file, and email it to me? I will PM you my email address.

 

[R1chard]

on xp i get the message:

Unable to check cancellation dates [could not open file].

In the code what does cancellation dates refer to? Because no where on my spreadsheet does it say cancellation dates

 

[TommyGun]

Okay, this message means that the file path inside the code does not exist. Where is your cancellation diary file saved?

 

[R1chard]

C:/WINDOWS/personal/Cancellation Diary.xls

 

[R1chard]

Tommy.......................IT WORKS!!! Thanks alot mate, thanks for taking the time to help me out.

 

[TommyGun]

No problem.

 

[raajeevmishra]

Okay, here is the adjusted code. You will probably need to save the VBS file into the startup directory to get this to run automatically.

Dim XLObject, XLFile, Due, sh, C 

On Error Resume Next 
Set XLObject = CreateObject("Excel.Application") 
If XLObject is Nothing Then 
   Msgbox "Unable to check cancellation dates [could not start Excel].", _ 
      vbOkOnly+vbCritical,"Cancellation Dates" 
Else 
   On Error Resume Next 
   Set XLFile = XLObject.Workbooks.Open("C:\WINDOWS\Personal\Cancellation Diary.xls")
   If XLFile is Nothing Then 
      Msgbox "Unable to check cancellation dates [could not open file].", _ 
         vbOkOnly+vbCritical,"Cancellation Dates" 

      XLObject.Quit 
      Set XLObject = Nothing 
   Else 
      With XLFile
	For Each sh in .Sheets
	 With sh
          For Each C in .Range(.Range("G1"),.Range("G65536").End(-4162)).Cells 
             If C.Value = Date Then 
                Due = True 
                Exit For 
             Else 
                Due = False 
             End If 
          Next 
	  If Due Then Exit For
         End With
        Next
      End With 

      XLFile.Close False 
      Set XLFile = Nothing 

      XLObject.Quit 
      Set XLObject = Nothing 

      If Due Then 
         MsgBox "There are cancellations due today!", _ 
            vbInformation+vbOkOnly,"Cancellation Check" 
      Else 
         MsgBox "There are no cancellations due today.", _ 
            vbOkOnly,"Cancellation Check" 
      End If 
   End If 
End If

What does -4162 stands for in (For Each C in .Range(.Range("C1"),.Range("C65536").End(-4162)).Cells)? <?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o> </o>
I am using the range E4 to E313. Although today’s date is not present in the said range, I am getting the popup message "There are cancellations due today!".