PDA

View Full Version : savecopyas in event



tinamiller1
02-25-2014, 02:59 PM
I am trying to savecopyas where the user can choose their own place for the file and their own name but the ext .xlsm but keep the original workbook open. Here is my code and I get the savecopyas pop-up and it is all files but do not want the end-user to have to put in filename.xlsm. want the .xlsm to already be selected and they just type in the actual name




Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim fName
Cancel = True

ActiveSheet.Unprotect
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.EnableEvents = False

fName = Application.GetSaveAsFilename
If InStr(1, UCase(fName), UCase(ThisWorkbook.Name)) Then
MsgBox "Sorry, you may not save as the existing name."
Exit Sub
End If
ThisWorkbook.SaveCopyAs (fName)
Call ClearHighlight
Call ClearMerged
Call ClearColor
Application.ScreenUpdating = True
Application.DisplayAlerts = True
Application.EnableEvents = True
ActiveSheet.Protect
End Sub

lecxe
02-25-2014, 07:14 PM
Hi

If I understood correctly, you just want to type the name of the file and the extension will be added automatically. Try:


fName = Application.GetSaveAsFilename("", "Excel Macro-Enabled workbook (*.xlsm), *.xlsm")