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
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