Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
display alert when workbook open
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
display alert when workbook open
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" wrote in message ... 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
display alert when workbook open
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" wrote in message ... 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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
display alert when workbook open
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" wrote in message ... 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" wrote in message ... 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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
display alert when workbook open
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" wrote in message ... 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" wrote in message ... 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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
display alert when workbook open
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" wrote in message ... 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" wrote in message ... 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" wrote in message ... 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
macro to display message,when open workbook | Excel Discussion (Misc queries) | |||
Conditional Format to display text alert | Excel Worksheet Functions | |||
Display Alert | Excel Programming | |||
Display an Alert popup. | Excel Programming | |||
Delete Display Alert tagged to an individual cell | Excel Programming |