Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
BeforeSave
Hi,
I need some more help with this code when msg box appears do you want to save as etc I click yes thats fine it saves as the new filename, but when I click No it is still saving changes in workbook to old filename, I need the workbook to close without any changes made if No is selected in msg box, can you help with this. Here is the code: End Sub 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 = "Kelso Resources WC " & Format(Sheets("Kelso Resources").Range("N2"), "dd-mmm-yy") userResponse = MsgBox("Do you want to save as " & strFilename, vbYesNo + vbInformation, "Kelso 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 = False End If Application.EnableEvents = False End Sub Regards Mark |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
BeforeSave
see if this does what you want?
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 = "Kelso Resources WC " & _ Format(Sheets("KelsoResources").Range("N2"), "dd-mmm-yy") userResponse = MsgBox("Do you want to save as " & _ strFilename, vbYesNo + vbInformation, _ "Kelso 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 = False Else Cancel = True End If Application.EnableEvents = True End Sub -- jb "terilad" wrote: Hi, I need some more help with this code when msg box appears do you want to save as etc I click yes thats fine it saves as the new filename, but when I click No it is still saving changes in workbook to old filename, I need the workbook to close without any changes made if No is selected in msg box, can you help with this. Here is the code: End Sub 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 = "Kelso Resources WC " & Format(Sheets("Kelso Resources").Range("N2"), "dd-mmm-yy") userResponse = MsgBox("Do you want to save as " & strFilename, vbYesNo + vbInformation, "Kelso 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 = False End If Application.EnableEvents = False End Sub Regards Mark |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
BeforeSave
This code has caused Microsoft excel to stop working when I click save as new
filename. Mark "john" wrote: see if this does what you want? 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 = "Kelso Resources WC " & _ Format(Sheets("KelsoResources").Range("N2"), "dd-mmm-yy") userResponse = MsgBox("Do you want to save as " & _ strFilename, vbYesNo + vbInformation, _ "Kelso 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 = False Else Cancel = True End If Application.EnableEvents = True End Sub -- jb "terilad" wrote: Hi, I need some more help with this code when msg box appears do you want to save as etc I click yes thats fine it saves as the new filename, but when I click No it is still saving changes in workbook to old filename, I need the workbook to close without any changes made if No is selected in msg box, can you help with this. Here is the code: End Sub 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 = "Kelso Resources WC " & Format(Sheets("Kelso Resources").Range("N2"), "dd-mmm-yy") userResponse = MsgBox("Do you want to save as " & strFilename, vbYesNo + vbInformation, "Kelso 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 = False End If Application.EnableEvents = False End Sub Regards Mark |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
BeforeSave
sorry, did not fully read your post - you want the file to close without
saving changes when NO press? 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 = "Kelso Resources WC " & _ Format(Sheets("KelsoResources").Range("N2"), "dd-mmm-yy") userResponse = MsgBox("Do you want to save as " & _ strFilename, vbYesNo + vbInformation, _ "Kelso 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 = False Else Cancel = True Application.EnableEvents = True ThisWorkbook.Close False End If Application.EnableEvents = True End Sub -- jb "terilad" wrote: Hi, I need some more help with this code when msg box appears do you want to save as etc I click yes thats fine it saves as the new filename, but when I click No it is still saving changes in workbook to old filename, I need the workbook to close without any changes made if No is selected in msg box, can you help with this. Here is the code: End Sub 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 = "Kelso Resources WC " & Format(Sheets("Kelso Resources").Range("N2"), "dd-mmm-yy") userResponse = MsgBox("Do you want to save as " & strFilename, vbYesNo + vbInformation, "Kelso 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 = False End If Application.EnableEvents = False End Sub Regards Mark |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
BeforeSave
How do you mean stop working? Only change I suggested was to add Else Cancel = True in your If statement. -- jb "terilad" wrote: This code has caused Microsoft excel to stop working when I click save as new filename. Mark "john" wrote: see if this does what you want? 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 = "Kelso Resources WC " & _ Format(Sheets("KelsoResources").Range("N2"), "dd-mmm-yy") userResponse = MsgBox("Do you want to save as " & _ strFilename, vbYesNo + vbInformation, _ "Kelso 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 = False Else Cancel = True End If Application.EnableEvents = True End Sub -- jb "terilad" wrote: Hi, I need some more help with this code when msg box appears do you want to save as etc I click yes thats fine it saves as the new filename, but when I click No it is still saving changes in workbook to old filename, I need the workbook to close without any changes made if No is selected in msg box, can you help with this. Here is the code: End Sub 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 = "Kelso Resources WC " & Format(Sheets("Kelso Resources").Range("N2"), "dd-mmm-yy") userResponse = MsgBox("Do you want to save as " & strFilename, vbYesNo + vbInformation, "Kelso 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 = False End If Application.EnableEvents = False End Sub Regards Mark |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
BeforeSave
This code is still causing excel to stop working when selected yes to saveas
new filename, I think it may have something to do with trying to recover workbook, as it is being saved as a new name. Any ideas how to resolve this issue Mark "john" wrote: sorry, did not fully read your post - you want the file to close without saving changes when NO press? 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 = "Kelso Resources WC " & _ Format(Sheets("KelsoResources").Range("N2"), "dd-mmm-yy") userResponse = MsgBox("Do you want to save as " & _ strFilename, vbYesNo + vbInformation, _ "Kelso 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 = False Else Cancel = True Application.EnableEvents = True ThisWorkbook.Close False End If Application.EnableEvents = True End Sub -- jb "terilad" wrote: Hi, I need some more help with this code when msg box appears do you want to save as etc I click yes thats fine it saves as the new filename, but when I click No it is still saving changes in workbook to old filename, I need the workbook to close without any changes made if No is selected in msg box, can you help with this. Here is the code: End Sub 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 = "Kelso Resources WC " & Format(Sheets("Kelso Resources").Range("N2"), "dd-mmm-yy") userResponse = MsgBox("Do you want to save as " & strFilename, vbYesNo + vbInformation, "Kelso 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 = False End If Application.EnableEvents = False End Sub Regards Mark |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
BeforeSave
Excel is obviously creating a backup because of this code as when I open the
new workbook that has saved with new filename it is also opening previous workbook as backup. Any ideas how to resolve. Mark "john" wrote: sorry, did not fully read your post - you want the file to close without saving changes when NO press? 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 = "Kelso Resources WC " & _ Format(Sheets("KelsoResources").Range("N2"), "dd-mmm-yy") userResponse = MsgBox("Do you want to save as " & _ strFilename, vbYesNo + vbInformation, _ "Kelso 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 = False Else Cancel = True Application.EnableEvents = True ThisWorkbook.Close False End If Application.EnableEvents = True End Sub -- jb "terilad" wrote: Hi, I need some more help with this code when msg box appears do you want to save as etc I click yes thats fine it saves as the new filename, but when I click No it is still saving changes in workbook to old filename, I need the workbook to close without any changes made if No is selected in msg box, can you help with this. Here is the code: End Sub 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 = "Kelso Resources WC " & Format(Sheets("Kelso Resources").Range("N2"), "dd-mmm-yy") userResponse = MsgBox("Do you want to save as " & strFilename, vbYesNo + vbInformation, "Kelso 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 = False End If Application.EnableEvents = False End Sub Regards Mark |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
BeforeSave
Fixed it, changed the cancel = true and false round and works
"john" wrote: sorry, did not fully read your post - you want the file to close without saving changes when NO press? 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 = "Kelso Resources WC " & _ Format(Sheets("KelsoResources").Range("N2"), "dd-mmm-yy") userResponse = MsgBox("Do you want to save as " & _ strFilename, vbYesNo + vbInformation, _ "Kelso 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 = False Else Cancel = True Application.EnableEvents = True ThisWorkbook.Close False End If Application.EnableEvents = True End Sub -- jb "terilad" wrote: Hi, I need some more help with this code when msg box appears do you want to save as etc I click yes thats fine it saves as the new filename, but when I click No it is still saving changes in workbook to old filename, I need the workbook to close without any changes made if No is selected in msg box, can you help with this. Here is the code: End Sub 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 = "Kelso Resources WC " & Format(Sheets("Kelso Resources").Range("N2"), "dd-mmm-yy") userResponse = MsgBox("Do you want to save as " & strFilename, vbYesNo + vbInformation, "Kelso 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 = False End If Application.EnableEvents = False End Sub Regards Mark |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
beforesave | Excel Programming | |||
beforesave | Excel Programming | |||
BeforeSave | Excel Programming | |||
BeforeSave Sub | Excel Programming | |||
VBA - BeforeSave - NEED HELP | Excel Programming |