Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
BeforeSave Event
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
BeforeSave Event
Excellant many thanks. Regards Mark "Jacob Skaria" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
BeforeSave Event
You didn't like yesterday's suggestions? 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 -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
BeforeSave Event
Hi Dave, I couldn't get yesterdays solutions or codes to work as stated in todays discussion, and I was looking for help to revise my code as I was having problems with 2 pop up boxes and excel stopping. I did rate your answers on the 8th with your 2 responses and altered my code accordingly as Sheet1 had a different name and my file path was missing an s from user, so your responses to my questions were of great help, code only needed additional slight modification to resolve the issues with 2 pop up boxes and excel stopping. I am learning slowly with VBA. Thankyou again for your input, all help is greatfully appreciated. Thanks "Dave Peterson" wrote: You didn't like yesterday's suggestions? 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 -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
BeforeSave Event
You may want to review yesterday's suggestion. There were some things in there that may be useful. terilad wrote: Hi Dave, I couldn't get yesterdays solutions or codes to work as stated in todays discussion, and I was looking for help to revise my code as I was having problems with 2 pop up boxes and excel stopping. I did rate your answers on the 8th with your 2 responses and altered my code accordingly as Sheet1 had a different name and my file path was missing an s from user, so your responses to my questions were of great help, code only needed additional slight modification to resolve the issues with 2 pop up boxes and excel stopping. I am learning slowly with VBA. Thankyou again for your input, all help is greatfully appreciated. Thanks "Dave Peterson" wrote: You didn't like yesterday's suggestions? 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 -- Dave Peterson -- Dave Peterson |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
BeforeSave Event
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 "Jacob Skaria" wrote: 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 | |||
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 Programming |