restrict the save as type options in an excel workbook

[dan.mcelligott]

Is there any way within an Excel workbook, to restrict the save as
type options in an excel spreadsheet? Particularly, I do not want
anyone to be able to change from 2003 workbook to 2007.

Thanks

Dan

 

[Héctor Miguel]

hi, Dan !

Is there any way within an Excel workbook, to restrict the save as type options in an excel spreadsheet?
Particularly, I do not want anyone to be able to change from 2003 workbook to 2007.

it is assumed your workbook is already saved as 97/2003 excel version...

1) put this in your workbook code module (ThisWorkbook)

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Application.OnTime Now, "CheckFileFormat"
End Sub

2) put this in a standard code module

Option Private Module
Sub CheckFileFormat()
If Val(Application.Version) > 11 Then
With ThisWorkbook
If .FileFormat = xlExcel8 Then Exit Sub
Dim InvalidFormat As String, JustTheName As String
InvalidFormat = .FullName
JustTheName = Left(.Name, InStrRev(.Name, ".") - 1)
Application.DisplayAlerts = False
.SaveAs JustTheName, xlExcel8
Application.DisplayAlerts = False
Kill InvalidFormat
End With
End If
End Sub

hth,
hector.