restrict the save as type options in an excel workbook

  • Thread starter Thread starter dan.mcelligott
  • Start date Start date
D

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
 
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.
Click to expand...

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.
 
Back
Top