Save dialog box

[Johnnyboy5]

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

 

[Per Jessen]

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

 

[Johnnyboy5]

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



- 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

 

[Johnnyboy5]

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

 

[Per Jessen]

Use the line below to save as .xls only:

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

Regards,
Per

 

[Per Jessen]

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

 

[Johnnyboy5]

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

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

much appreciated

John