Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Save
Hi, I have the following macro that saves as a filename when closed, how can I also add to the code for the fil to save as when the save icon is clicked as well along with the close icon. Private Sub Workbook_BeforeClose(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 + vbDefaultButton2 + vbQuestion, "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 Many thanks Mark |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Save
Use the BeforeSave event, make sure that you set Cancel = True -- __________________________________ HTH Bob "terilad" wrote in message ... Hi, I have the following macro that saves as a filename when closed, how can I also add to the code for the fil to save as when the save icon is clicked as well along with the close icon. Private Sub Workbook_BeforeClose(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 + vbDefaultButton2 + vbQuestion, "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 Many thanks Mark |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Save
Hi Bob, This is my code I have input. 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, also I didn't understand the part set Cancel = True can you explain this part. Regards Mark "Bob Phillips" wrote: Use the BeforeSave event, make sure that you set Cancel = True -- __________________________________ HTH Bob "terilad" wrote in message ... Hi, I have the following macro that saves as a filename when closed, how can I also add to the code for the fil to save as when the save icon is clicked as well along with the close icon. Private Sub Workbook_BeforeClose(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 + vbDefaultButton2 + vbQuestion, "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 Many thanks Mark |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Save
Cancel = true
means that you don't want excel to actually process the Save that the user started when he/she clicked the save button. If you don't cancel that process, then excel will want to save the file, too. Option Explicit 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(Me.Sheets("Galashiels Resources") _ .Range("N2").Value, "dd-mmm-yy") userResponse = MsgBox(prompt:="Do you want to save as " & strFilename, _ Buttons:=vbYesNo + vbCritical, _ Title:="Galashiels Operational Resources © MN") If userResponse = vbYes Then Cancel = True strPath = "C:\Users\Mark\Desktop" strFilename = strPath & "\" & strFilename & ".xls" 'stop the _beforesave event from firing because 'of the .saveas Application.EnableEvents = False On Error Resume Next Me.SaveAs Filename:=strFilename, _ FileFormat:=xlNormal, _ CreateBackup:=False If Err.Number < 0 Then MsgBox "SaveAs failed" & vblf & err.Number & vblf & err.Description Err.Clear End If On Error GoTo 0 'start monitoring events again Application.EnableEvents = False End If End Sub Compiled, but untested. I also qualified the sheet so that it refered to the workbook being saved. This code is in the ThisWorkbook module, so the Me keyword refers to that workbook. (I also changed the .saveas line to use Me instead of ThisWorkbook.) And the bigger change is the .enableevents line. This stops the .SaveAs from starting the _beforeSave event. I also added a check to see if the .saveas was successful. If the filename wasn't valid, the save may not work and bad things could happen. terilad wrote: Hi Bob, This is my code I have input. 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, also I didn't understand the part set Cancel = True can you explain this part. Regards Mark "Bob Phillips" wrote: Use the BeforeSave event, make sure that you set Cancel = True -- __________________________________ HTH Bob "terilad" wrote in message ... Hi, I have the following macro that saves as a filename when closed, how can I also add to the code for the fil to save as when the save icon is clicked as well along with the close icon. Private Sub Workbook_BeforeClose(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 + vbDefaultButton2 + vbQuestion, "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 Many thanks Mark -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Save, save as, page setup dimmed out in unprotected excel sheet? | Excel Discussion (Misc queries) | |||
prompt user to save file as {desired_name} and save it to a variab | Excel Programming | |||
Disable save, save as, but allow save via command button | Excel Programming | |||
How to diasble save and save as menu but allow a save button | Excel Programming | |||
Totally Disabling (^ save ) (Save as) and Save Icon – Which code do I use: | Excel Programming |