Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
BeforeSave Event
Try the below code....Please note the changes Application.EnableEvents = True/False and 'Cacel =True' Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) Dim strPath As String Dim strFilename As String Dim userResponse As Variant Application.EnableEvents = False strFilename = "Galashiels Resources WC " & Format(Sheets("Galashiels Resources").Range("N2"), "dd-mmm-yy") userResponse = MsgBox("Do you want to save as " & strFilename, vbYesNo + vbCritical, "Galashiels Operational Resources © MN ") If userResponse = vbYes Then strPath = "C:\Users\Mark\Desktop" strFilename = strPath & "\" & strFilename & ".xls" ThisWorkbook.SaveAs Filename:=strFilename, FileFormat:=xlNormal, CreateBackup:=False Cancel = True End If Application.EnableEvents = True End Sub -- If this post helps click Yes --------------- Jacob Skaria "terilad" wrote: Hi, I have a code below and have a little problem with it, its not running smoothly for me. Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) Dim strPath As String Dim strFilename As String Dim userResponse As Variant strFilename = "Galashiels Resources WC " & Format(Sheets("Galashiels Resources").Range("N2"), "dd-mmm-yy") userResponse = MsgBox("Do you want to save as " & strFilename, vbYesNo + vbCritical, "Galashiels Operational Resources © MN ") If userResponse = vbYes Then strPath = "C:\Users\Mark\Desktop" strFilename = strPath & "\" & strFilename & ".xls" ThisWorkbook.SaveAs Filename:=strFilename, FileFormat:=xlNormal, CreateBackup:=False End If End Sub Before save event, when I click on save it pops up the msg box do you want to save as, when I click yes it pops up again and excel stops working, do you have any ideas if my code is wrong. Regards Mark |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
BeforeSave event | Excel Programming | |||
How do I change a Worksheet_change event to a beforesave event? | Excel Programming | |||
Need help with BeforeSave event | Excel Programming | |||
BeforeSave event | Excel Discussion (Misc queries) | |||
BeforeSave event | Excel Programming |