PDA

View Full Version : Prevent Save, enable SaveAs with default file name



chex
01-15-2009, 11:34 AM
hello all VBA/Excel experts, I am new to this forum and asking for your help. Here is what I am trying to do:
1) if a user clicks Save or Ctrl-S, a message should come up saying that this is not allowed, as it would overwrite the existing file (this works)
2) if a user clicks Save As, it should suggest a default file name (Consisting of a value in a cell + date) and it should save the file with either the suggested name or a new name (this does not work, a message from Step 1 comes up). Below is the code. Thank you so much for your help and have a great day.



Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
If SaveAsUI = False Then
Cancel = True
MsgBox "This would overwrite the template. Use 'Save As' instead."
Exit Sub
End If
SaveAsUI = True
Dim oFileDialog As FileDialog
Set oFileDialog = Application.FileDialog(msoFileDialogSaveAs)
Dim strName As String
Dim strDate As String
Dim strCustName As String
Dim strShortCustName As String
Dim myPath As String
Cancel = False
myPath = ActiveWorkbook.Path
strCustName = Sheet4.Range("C4")
strShortCustName = Left(strCustName, 6)
strDate = Format(Date, "Medium Date")
strName = myPath & "/" & strShortCustName & " " & strDate & ".xls"
With oFileDialog
SaveAsUI = True
.InitialFileName = strName
.Show
.Execute
Cancel = False
End With
End Sub

:banghead:

lucas
01-15-2009, 11:46 AM
Use a template.......

They open the template and a clone of it is opened for them to use and save any way they want.