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