Popup message

[sjoerdieman]

Hi guys,

I'm making a plan which includes some dates

First row: Last date of inspection ( manual editing )
Second row: Next date of inspection ( adds 28 days to the first row )
Third row: days until next inspection ( second row min the date of today )

I want excel to give a popup message when i start up this excel file when the third row reach 5 days.

So if there are 5 days before the next inspection i want to give excel a popup message like: 5 days until inspection. or something like that. I've already found some VBA codes for giving a popup message on the moment you change the value of a cell, but i want to popup at the start of the excel file.

Sorry for my english, but I hope you can help me!

 

[ArthriticPanda]

Hi sjoerdieman,

I've created a quick sample for you with set dates so you can see it working.

Excel Workbook
	A	B	C	D	E
1	Days Until Next Inspection		Current Date		5
2	18/01/2014		13/01/2014
Sheet1

You then need to locate the excel object 'ThisWorkbook' in the VBA code window and place the following code in it:

Private Sub Workbook_Open()


If Range("E1") = 5 Then MsgBox "Five days to next inspection", vbInformation, "Next Inspection"


End Sub

Hopefully this will give you an idea on how to set it up.

Regards,

AP

 

[sjoerdieman]

Hi sjoerdieman,

I've created a quick sample for you with set dates so you can see it working.

Sheet1

*	A	B	C	D	E
Days Until Next Inspection	*	Current Date	*
*	*	*

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:181px;"><col style="width:22px;"><col style="width:80px;"><col style="width:64px;"><col style="width:71px;"></colgroup><tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]

[TD="align: right"]5[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]
[TD="align: right"]18/01/2014[/TD]

[TD="align: right"]13/01/2014[/TD]

</tbody>

Spreadsheet Formulas

Cell Formula E1 =A2-C2 <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

You then need to locate the excel object 'ThisWorkbook' in the VBA code window and place the following code in it:

Private Sub Workbook_Open()


If Range("E1") = 5 Then MsgBox "Five days to next inspection", vbInformation, "Next Inspection"


End Sub

Hopefully this will give you an idea on how to set it up.

Regards,

AP

Thanks for your respone i'll give it a try tomorrow at my internship

 

[sjoerdieman]

Its working, but i want it a little bit different.
Now it keeps popping up when ever i click in excel. I just want to pop it up once at the startup of the file.

 

[sjoerdieman]

anyone?

 

[Stiuart_W]

Make sure that the code is in the Workbook open event.

This will run only once when the workbook is opened (assuming macros have been enabled) or when macros are allowed.

It sounds to me like you have your code in the Worksheet change or click or calculate event

 

[ArthriticPanda]

Its working, but i want it a little bit different.
Now it keeps popping up when ever i click in excel. I just want to pop it up once at the startup of the file.

From what you describe I think Stiuart_W is correct. Can you check there is no other code entered in the sheet you are working on, for example if you are working on sheet1, make sure there is nothing in the following code section:

Private Sub Worksheet_Change(ByVal Target As Range)


End Sub

AP