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]

Okay...this requires that your machine is able to run VB script files.

Take the following code and paste it into an empty text file (using notepad). Save this file as a VBS file (i.e. cancellation_check.vbs), and place it in your Windows Startup directory.

This is a VB script file that will run each time you log into your computer (as long as it is saved in the proper location).

Oh, and be sure to edit the filename in the code to the correct filename for your cancellation file.

Now...here is the code...

Dim XLObject, XLFile, Due, 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:\VBA Code\CancellationDates.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.Sheets(1)
			For Each C in .Range(.Range("C1"),.Range("C65536").End(-4162)).Cells
				If C.Value = Date Then
					Due = True
					Exit For
				Else
					Due = False
				End If
			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

 

[R1chard]

sorry to be thick, but what do i need to change in the above formula?

 

[TommyGun]

This piece...

("C:\VBA Code\CancellationDates.xls")

Specifies the file to be opened (to check for cancellations). You need to edit that to your file path. For example...

("C:\BusinessFile\Cancel Dates.xls")

 

[R1chard]

thanks alot for your help. I will let you know if it worked ok. Might be a while, bit busy.

Really appreciate the help

:wink:

 

[R1chard]

I have saved the code in note pad. The path to the file is:

C:\WINDOWS\Personal\cancellation_check.vbs

My Excel spreadsheet is saved as Cancellation Diary.xls The path to this file is:

C:\WINDOWS\Personal\Cancellation Diary.xls

Sorry bud, I didn't explain the layout of my spreadsheet properly on the previous post.

I have 12 sheets, all identical to each other. The cancellation dates are in column G. Sheets are named January to December, and i need it to search all sheets, to see if there is a cancellation due.

Just wondering if you could adjust the formula you previously posted for my new request with the correct file names. Don't worry if you can't

Much Appreciated :wink:

 

[TommyGun]

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

 

[R1chard]

thats excellent. thanks very much! :wink:

 

[TommyGun]

So it ran okay then?

 

[R1chard]

Sorry mate, i was saying thanks for having another look at it for me. Ive just tested it and i get an error box pop up. The message is displayed below:

Windows Scripting Host - Script Execution Error
Script: C:\WINDOWS\Personal\cancellation_check.vbs
Line Number: 18 Column: 6
Category: Microsoft VBScript compilation error
Description: Expected statement

Any ideas?