![]() |
Save dialog box
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 |
Save dialog box
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 |
Save dialog box
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 |
Save dialog box
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 |
Save dialog box
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 - |
Save dialog box
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 - |
Save dialog box
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 |
All times are GMT +1. The time now is 12:15 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com