Protecting an Excel 2007 workbook with a warning before opening

[mike_vr]

Morning all

We have upgraded to Office 2007 within our office, but not across the
company yet. There are a number of worksheets that I have created using the
new functions (e.g. SumIfs) that aren't available in earlier versions of
Excel, so that when other users open them in earlier versions it returns the
#Name? result in the cell and effectively buggers up my formulae.

Is there a way to have a warning pop up as someone is about to open the
workbook informing them that if they do not have Excel 2007 to abort and
contact me instead? I.e. have a proceed or cancel option before opening the
workbook, but not have to rely on Password protecting.

Thanks

Mike

 

[Bob Phillips]

Sub TestFor2007()
If Val(Application.Version) < 12 Then
MsgBox "This workbook relies on Excel 2007," & vbNewLine & _
"and your version is lower." & vbNewLine & vbNewLine & _
"Becuase of this it will not work properly."
End If
End Sub


Call this from the Workbook_Open event procedure.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[mike_vr]

Hi Bob, thanks for getting back to me.

This is almost what I was after, but not quite. This code still allows the
user to open it, after which point the formulae get reverted to the =#N/A
even though the value is still visible. When this is now re-opened in 2007
the formula is lost (as has been my experience, but maybe I'm missing
something here?!?)

So what I'm really looking for is something like the workbook_open procedure
below but that actually stops them from opening it and giving them a message
to contact me instead before it even opens. Is this possible???

Thanks again for your help,

Mike