If you want to make the default for new workbooks have that option checked, you
can create a new workbook and fix every setting that you want--including this
one.
Save the workbook in your XLStart folder and name it book.xlt.
If you want to find out where that folder is:
Open excel
hit alt-f11 to get to the VBE (where macros live)
hit ctrl-g to see the immediate window.
type this and hit enter:
?application.StartupPath
For me (winXP and xl2003), I get:
C:\Documents and Settings\(username)\Application Data\Microsoft\Excel\XLSTART
If you want to really intercept the save of every workbook--not just the new
ones created using that book.xlt as the template, you're gonna need a workbook
that is always open when you open excel.
Then you can build an application event that looks for the user doing a save.
Start a new workbook
hit alt-f11 to see the VBE (where macros live)
hit ctrl-r to see the project explorer (like windows explorer)
Find your workbook's name and select it.
hit the asterisk on the numeric keypad to expand all the "components" of the
project.
Look for ThisWorkbook and double click on it.
This is where the the workbook_beforeclose and workbook_open procedure would go.
Paste this into the code window:
Option Explicit
Public WithEvents xlApp As Excel.Application
Private Sub Workbook_Open()
Set xlApp = Application
End Sub
Private Sub Workbook_Close()
Set xlApp = Nothing
End Sub
Private Sub xlApp_WorkbookBeforeSave(ByVal Wb As Workbook, _
ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim myFileName As Variant
Dim testStr As String
Dim resp As Long
myFileName = Wb.FullName
If SaveAsUI Then
myFileName = Application.GetSaveAsFilename _
(InitialFileName:=Wb.FullName, _
filefilter:="Excel file, *.xls")
If myFileName = False Then
Cancel = True
Exit Sub
Else
testStr = ""
On Error Resume Next
testStr = Dir(myFileName)
On Error GoTo 0
resp = vbYes
If testStr = "" Then
'do nothing
Else
resp = MsgBox(Prompt:="Overwrite Existing File?", _
Buttons:=vbYesNo)
If resp = vbNo Then
Cancel = True
Exit Sub
End If
End If
End If
End If
'do the actual save
With Application
.StatusBar = "Saving " & myFileName
.DisplayAlerts = False
.EnableEvents = False
End With
On Error Resume Next
Wb.SaveAs myFileName, FileFormat:=xlWorkbookNormal, CreateBackup:=True
If Err.Number <> 0 Then
MsgBox "Something went wrong. File not saved" & vbLf _
& Err.Number & "--" & Err.Description
Err.Clear
Else
MsgBox "Saved as an xl workbook as: " & myFileName
End If
With Application
.StatusBar = False
.DisplayAlerts = True
.EnableEvents = True
End With
Cancel = True 'we did the work, don't let excel do it again.
End Sub
============
I would save this as an addin--so I could turn it off when I don't want it (more
on that later).
File|SaveAs|choose "microsoft Office Add-In (*.xla)" from the "save as type:"
box.
You can save it into the addin folder that excel suggests/defaults to.
Now close excel (just to start fresh).
And reopen it.
Tools|Addins|and check that addin you just created.
=========
If you look at the code, you see that it only supports saving *.xls files with
this line:
Wb.SaveAs myFileName, FileFormat:=xlWorkbookNormal, CreateBackup:=True
You could lots of coding to make sure you support other fileformats--or take the
lazy way out.
If you need to save a file as .prn, .csv, .whateverelseyouwant, just turn off
the addin, save the file and turn the addin back on (if you want).
Tools|Addins
and toggle to your heart's content.
=========
If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm
You can read a lot more about application events at Chip Pearson's site:
http://www.cpearson.com/excel/AppEvent.htm