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)
"Rajesh" <(E-Mail Removed)> wrote in message
news:20E47E45-1450-43FE-A7CE-(E-Mail Removed)...
> 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" wrote:
>
>> 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)
>>
>>
>> "Rajesh" <(E-Mail Removed)> wrote in message
>> news:9C098EB6-D649-435F-AE2C-(E-Mail Removed)...
>> > Hi Philips,
>> >
>> > Thank you verymuch.
>> > but i'm getting an error like-Runtime error 9,Subscript out of range.
>> >
>> >
>> >
>> > "Bob Phillips" wrote:
>> >
>> >> 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)
>> >>
>> >>
>> >> "Rajesh" <(E-Mail Removed)> wrote in message
>> >> news:8B307CBA-27B9-4F35-BA82-(E-Mail Removed)...
>> >> > 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.
>> >>
>> >>
>> >>
>>
>>
>>
|
Similar Threads
