VB open file event [SOLVED]

[mr_hiboy]

Hello one and all,


Been looking pop a simple message on opening a file. Checks if the data is up to date, if note alerts me to update.


Tried this in thisworkbook object, but no joy?


Private Sub Worksheet_Open()

If ActiveWorkbook.Worksheets("FleetChanges").Range("Q4") > 7 Then

MsgBox "Data is >7 days old, update data"

End If

End Sub


Cheers

 

[Hui]

Mr Hiboy


Your code is ok


Are you saving the file as a *.xlsm or *.xlsb file type?


The file as a *.xlsx won't be able to use any included macros


Also what value is in Q4 ?

 

[mr_hiboy]

thanks for quick reply as usual.


xlsm file


Q4 has formula, =TODAY()+14-N4 (the +14 is so i can test today)


The result 13, therefore >7


thanks

 

[mr_hiboy]

actually Q4 was a cheat cell, i'd rather have done the calc in the code, but that wasn't working then tried the cheat cell

 

[Luke M]

A couple of things. I'd recommend changing the

ActiveWorkbook object to the [code]ThisWorkbook object. Makes things clearer, and helps prevent strange errors (like when workbook opens, but isn't the active one).

Next, I'd check the range in question (Q4). Is it really greater than 7?

Is it not getting calculated/updated till after the workbook finishes opening?

Perhaps a different check would be to check the date via VB's Date

object

[pre]If Date - ThisWorkbook.Worksheets("FleetChanges&#34.Range("Q3&#34 > 7 Then
MsgBox "Old"
End If[/code][/pre]

 

[mr_hiboy]

ah ha school boy error, I had Private Sub Worksheet_Open() changed it to workbook!


Worked a treat, also added the calc in the code Luke, cheers


Thanks again

 

[Luke M]

Ah, glad it was a simple fix. Sorry we missed it too!