display alert when workbook open

[Guest]

Hi,
I've a Excel sheet with customer name and the contract end date.wht i'm
looking for is i want to get a alert message whn open the workbook 1 week
before the contract end date.

 

[Bob Phillips]

Private Sub Workbook_Open()
With ThisWorkbook.Worksheets("check").Range("A1")
If .Value >= Date - 7 And .Value <= Date Then
MsgBox "Contract ends on " & Format(.Value, "dd mmm yyyy")
End If
End With
End Sub

'This is workbook event code.
'To input this code, right click on the Excel icon on the worksheet
'(or next to the File menu if you maximise your workbooks),
'select View Code from the menu, and paste the code


--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)

 

[Guest]

Hi Philips,

Thank you verymuch.
but i'm getting an error like-Runtime error 9,Subscript out of range.

 

[Bob Phillips]

I used a worksheet name of check, you will need to change it to the actual
name.

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)

 

[Guest]

Thanks Bob,

i got that, Thank you verymuch.
and one more issue, hw do i select the entire column,here its only for
A1,when i tried like A1:A100,this giving some error 13, type mismatch.

 

[Bob Phillips]

Private Sub Workbook_Open()
Dim sMsg As String
Dim cell As range

With ThisWorkbook.Worksheets("check")
For Each cell In .Range("A1:A100")
If cell.Value >= Date - 7 And cell.Value <= Date Then
sMsg = sMsg & "Contract in " & cell.Address(False, False) &
_
" ends on " & Format(cell.Value, "dd mmm
yyyy") & vbNewLine
End If
Next cell
End With

If sMsg <> "" Then MsgBox sMsg

End Sub


--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)