Disable the save button

[leerem]

Hi,
I need to be able to force the user to save the spreadsheet by use of
VBA as opposed to useing the save button or using the File\ save in excel
2003 or Office button ect in excel 2007. This is so that i can keep the
original file as an empty form, and to ensure the file is saved in the
correct location.

many thanks
lee

 

[Mike H]

Hi,

cancel the save event and call your save macro, something like this

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Cancel = True
mysave
End Sub

Sub mysave()
Application.EnableEvents = False
'do things and save

Application.EnableEvents = True
End Sub

Mike

 

[leerem]

Hi Mike,

I take the the first routine will have to be entered in the
ThisWorkbook ?

 

[Jim Rech]

Workbook_BeforeSave has to go in ThisWorkbook. The other sub can go there
or in a standard module.

--
Jim
| Hi Mike,
|
| I take the the first routine will have to be entered in the
| ThisWorkbook ?
|
| "Mike H" wrote:
|
| > Hi,
| >
| > cancel the save event and call your save macro, something like this
| >
| > Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
| > Cancel = True
| > mysave
| > End Sub
| >
| > Sub mysave()
| > Application.EnableEvents = False
| > 'do things and save
| >
| > Application.EnableEvents = True
| > End Sub
| >
| > Mike
| >
| > "leerem" wrote:
| >
| > > Hi,
| > > I need to be able to force the user to save the spreadsheet by
use of
| > > VBA as opposed to useing the save button or using the File\ save in
excel
| > > 2003 or Office button ect in excel 2007. This is so that i can keep
the
| > > original file as an empty form, and to ensure the file is saved in the
| > > correct location.
| > >
| > > many thanks
| > > lee

 

[leerem]

many thanks Mike just what i needed
regards
Lee

Hi,

cancel the save event and call your save macro, something like this

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Cancel = True
mysave
End Sub

Sub mysave()
Application.EnableEvents = False
'do things and save

Application.EnableEvents = True
End Sub

Mike