tbakbradley
Board Regular
- Joined
- Sep 24, 2010
- Messages
- 136
I have a macro (simplified it here) that performs many activities, but then presents the User with the Save As Dialog Box to save the file. Once the file is saved, additional steps happen. I wanted a method to stop the Macro if the User Canceled the Save As because if not, the current form would be emailed to another team without the proper data on the current saved file.
I now have a need to Force a File Name. I will need to set up some Variables based on Cells in the Spreadsheet. File name would have to say be "Case Number_Service" where both of those are stored in the spreadsheet as they change per Request.
I'm having difficulties since I had to prevent the macro from moving forward if the User canceled the Save As. Is there anything I can do with this code to force a file to be saved with a specific name based on variables (user would select path)?
I now have a need to Force a File Name. I will need to set up some Variables based on Cells in the Spreadsheet. File name would have to say be "Case Number_Service" where both of those are stored in the spreadsheet as they change per Request.
I'm having difficulties since I had to prevent the macro from moving forward if the User canceled the Save As. Is there anything I can do with this code to force a file to be saved with a specific name based on variables (user would select path)?
Code:
Sub Build ()
Application.EnableEvents = False 'turn events off so the SaveAs Private Sub for Workbook is turned off and not to hinder Save As in this Macro
'Save Feature will end if Engineer clicks Cancel
[COLOR=#ff0000]X = Application.Dialogs(xlDialogSaveAs).Show
If X = False Then[/COLOR]
MsgBox "You Canceled your save. Therefore, this step cannot move forward or the Auto Emails would be sent with the incorrect Attachment.”
Sheets("Sheet 1").Select
Range("Y3").Select
Exit Sub
End If
Application.EnableEvents = True 'turn events back on - this is critical, otherwise no events will fire again
Sheets("Sheet 2").Select
Range("Z1").Select
EmailOptions.Show
End Sub