Hi
I have a sheet (not template) which is password protected - so when
a user has to use it they use read only and then should save it will
a
new file name. The problem is some users ! forget and save a “copy”
version of the original file name. I cant really use a template as
the default save it in templates and I want the new file saved in a
particular folder on the office Main frame.
I would like the “Save” box to be empty - (no file name in it) so
the
user has to type in the new file name.
Thanks
Johnny

Insert this in 'ThisWorkbook' code sheet.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
ChDir "C:\Temp" 'Path to save copy to
Do
fName = Application.GetSaveAsFilename("", , , "Enter filename")
Loop Until fName < "false"
Debug.Print fName
ThisWorkbook.SaveCopyAs fName
Cancel = True
End Sub

Regards,
Per

On 24 Nov., 17:05, Johnnyboy5 wrote:
Hi
I have a sheet (not template) which is password protected - so when
a user has to use it they use read only and then should save it will
a
new file name. *The problem is some users ! forget and save a “copy”
version of the original file name. * I cant really use a template as
the default save it in templates and I want the new file saved in a
particular folder on the office Main frame.
I would like the “Save” box to be empty - (no file name in it) *so
the
user has to type in the new file name.
Thanks
Johnny

On Nov 24, 5:16*pm, Per Jessen wrote:
Insert this in 'ThisWorkbook' code sheet.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
ChDir "C:\Temp" 'Path to save copy to
Do
* * fName = Application.GetSaveAsFilename("", , , "Enter filename")
Loop Until fName < "false"
Debug.Print fName
ThisWorkbook.SaveCopyAs fName
Cancel = True
End Sub

Regards,
Per

On 24 Nov., 17:05, Johnnyboy5 wrote:



Hi
I have a sheet (not template) which is password protected - so when
a user has to use it they use read only and then should save it will
a
new file name. *The problem is some users ! forget and save a “copy”
version of the original file name. * I cant really use a template as
the default save it in templates and I want the new file saved in a
particular folder on the office Main frame.
I would like the “Save” box to be empty - (no file name in it) *so
the
user has to type in the new file name.
Thanks
Johnny- Hide quoted text -

- Show quoted text -

Hi

thanks - cant get it work yet - might it being in conflict with
another macro in the "module"

Sub MySave()

Dim MyFileName As String
ChDir "S:\CCAS\DC\Reablement Team Goal Plans Etc\2 SPICE SHEETS"
MyFileName = Application.GetSaveAsFilename
If MyFileName = "false" Then Exit Sub
ActiveWorkbook.SaveAs MyFileName
End Sub

thanks

Johnny

On Nov 26, 12:12*pm, Johnnyboy5 wrote:
On Nov 24, 5:16*pm, Per Jessen wrote:





Insert this in 'ThisWorkbook' code sheet.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
ChDir "C:\Temp" 'Path to save copy to
Do
* * fName = Application.GetSaveAsFilename("", , , "Enter filename")
Loop Until fName < "false"
Debug.Print fName
ThisWorkbook.SaveCopyAs fName
Cancel = True
End Sub

Regards,
Per

On 24 Nov., 17:05, Johnnyboy5 wrote:

Hi
I have a sheet (not template) which is password protected - so when
a user has to use it they use read only and then should save it will
a
new file name. *The problem is some users ! forget and save a “copy”
version of the original file name. * I cant really use a template as
the default save it in templates and I want the new file saved in a
particular folder on the office Main frame.
I would like the “Save” box to be empty - (no file name in it) *so
the
user has to type in the new file name.
Thanks
Johnny- Hide quoted text -

- Show quoted text -

Hi

thanks - cant get it work yet *- might it being in conflict with
another macro in the "module"

Sub MySave()

Dim MyFileName As String
ChDir "S:\CCAS\DC\Reablement Team Goal Plans Etc\2 SPICE SHEETS"
MyFileName = Application.GetSaveAsFilename
If MyFileName = "false" Then Exit Sub
ActiveWorkbook.SaveAs MyFileName
End Sub

thanks

Johnny- Hide quoted text -

- Show quoted text -

How do I get it to save as an .xls file not an "all files" thing

thanks

John

Use the line below to save as .xls only:

fName = Application.GetSaveAsFilename("", "Excel Files (*.xls),
*.xls", , "Enter filename")

Regards,
Per


How do I get it to save as an .xls file not an "all files" *thing

thanks

John- Skjul tekst i anførselstegn -

- Vis tekst i anførselstegn -

The event macro will be called when MySave macro is executed. To avoid
the call to the Workbook_BeforeSave event, you can use the code as
below:

'*** In the module insert use this ***
'*** Notice that the Public declared variable has to be at the very
top of the module***
Public DisableSaveEvent As Boolean
Sub MySave()
Dim MyFileName As String
ChDir "S:\CCAS\DC\Reablement Team Goal Plans Etc\2 SPICE SHEETS"
MyFileName = Application.GetSaveAsFilename

If MyFileName = "false" Then Exit Sub
DisableSaveEvent = True
ActiveWorkbook.SaveAs MyFileName
End Sub

'*** In ThisWorkbook module use this code ***
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
If DisableSaveEvent Then Exit Sub
ChDir "C:\Temp" 'Path to save copy to
Do
fName = Application.GetSaveAsFilename("", fName =
Application.GetSaveAsFilename("", "Excel Files (*.xls), *.xls", ,
"Enter filename"), , "Enter filename")
Loop Until fName < "false"
Debug.Print fName
ThisWorkbook.SaveCopyAs fName
Cancel = True
End Sub

Regards,
Per

Hi

thanks - cant get it work yet *- might it being in conflict with
another macro in the "module"

Sub MySave()

Dim MyFileName As String
ChDir "S:\CCAS\DC\Reablement Team Goal Plans Etc\2 SPICE SHEETS"
MyFileName = Application.GetSaveAsFilename
If MyFileName = "false" Then Exit Sub
ActiveWorkbook.SaveAs MyFileName
End Sub

thanks

Johnny- Skjul tekst i anførselstegn -

- Vis tekst i anførselstegn -

On Nov 27, 8:57*am, Per Jessen wrote:
The event macro will be called when MySave macro is executed. To avoid
the call to the Workbook_BeforeSave event, you can use the code as
below:

'*** In the module insert use this ***
'*** Notice that the Public declared variable has to be at the very
top of the module***
Public DisableSaveEvent As Boolean
Sub MySave()
Dim MyFileName As String
ChDir "S:\CCAS\DC\Reablement Team Goal Plans Etc\2 SPICE SHEETS"
MyFileName = Application.GetSaveAsFilename

If MyFileName = "false" Then Exit Sub
DisableSaveEvent = True
ActiveWorkbook.SaveAs MyFileName
End Sub

'*** In ThisWorkbook module use this code ***
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
If DisableSaveEvent Then Exit Sub
ChDir "C:\Temp" 'Path to save copy to
Do
* * fName = Application.GetSaveAsFilename("", fName =
Application.GetSaveAsFilename("", "Excel Files (*.xls), *.xls", ,
"Enter filename"), , "Enter filename")
Loop Until fName < "false"
Debug.Print fName
ThisWorkbook.SaveCopyAs fName
Cancel = True
End Sub

Regards,
Per



Hi

thanks - cant get it work yet *- might it being in conflict with
another macro in the "module"

Sub MySave()

Dim MyFileName As String
ChDir "S:\CCAS\DC\Reablement Team Goal Plans Etc\2 SPICE SHEETS"
MyFileName = Application.GetSaveAsFilename
If MyFileName = "false" Then Exit Sub
ActiveWorkbook.SaveAs MyFileName
End Sub

thanks

Johnny- Skjul tekst i anførselstegn -

- Vis tekst i anførselstegn -

Thanks - will check it out in work next week and let you know how I
get on.

much appreciated

John