Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cancel = True
First of all, thanks everyone for the invaluable help you've provided me on
this message board! ...now.... code: Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) 'code to hide certain sheets Me.Save Cancel = True 'code to unhide certain sheets End Sub question: what exactly does the Cancel = True do (or not do)? I found that when I was trying to Save As a new filename that I was not being given the option to change the name. when I comment out the Cancel = True line in my BeforeSave procedure I am able to do save with a new filename when doing a Save As. I want to understand why this is so, so that I know that I am not inadvertently causing other potential problems by removing the Cancel = True. TIA Tanya (vba newbie) |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cancel = True
Hello Tanya,
Cancel=True in the BeforeSave event means cancel the save operation. If Cancel=False, Excel will proceed with saving your file at the end of the event. You need to have Cancel=True because you had already saved the workbook on the previous line and there's no need for Excel to save it again. Furthermore, if you take that line out, Excel will end up saving your workbook after you unhide the sheets. Try your BeforeSave code like this: Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) Dim strSaveAs As String Cancel = True If SaveAsUI = True Then 'get a save as filename from the user strSaveAs = Application.GetSaveAsFilename(Me.Name, "Excel Files (*.xls), *.xls", 1) If strSaveAs = "False" Then 'user cancelled the save as operation Exit Sub End If End If 'code to hide certain sheets Application.EnableEvents = False If SaveAsUI = True Then Me.SaveAs strSaveAs Else Me.Save End If Application.EnableEvents = True 'code to unhide certain sheets End Sub -- Hope that helps. Vergel Adriano "JustBreathe" wrote: First of all, thanks everyone for the invaluable help you've provided me on this message board! ...now.... code: Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) 'code to hide certain sheets Me.Save Cancel = True 'code to unhide certain sheets End Sub question: what exactly does the Cancel = True do (or not do)? I found that when I was trying to Save As a new filename that I was not being given the option to change the name. when I comment out the Cancel = True line in my BeforeSave procedure I am able to do save with a new filename when doing a Save As. I want to understand why this is so, so that I know that I am not inadvertently causing other potential problems by removing the Cancel = True. TIA Tanya (vba newbie) |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cancel = True
Awesome! I tested to see if what you said was happening was and you were
right. Then I was able fit your suggested code in and it is worked just right on the first try. .....I'm starting to scare myself that I'm actually able to decipher and apply the advice you folks are able to provide :P Tanya (newbie) "Vergel Adriano" wrote: Hello Tanya, Cancel=True in the BeforeSave event means cancel the save operation. If Cancel=False, Excel will proceed with saving your file at the end of the event. You need to have Cancel=True because you had already saved the workbook on the previous line and there's no need for Excel to save it again. Furthermore, if you take that line out, Excel will end up saving your workbook after you unhide the sheets. Try your BeforeSave code like this: Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) Dim strSaveAs As String Cancel = True If SaveAsUI = True Then 'get a save as filename from the user strSaveAs = Application.GetSaveAsFilename(Me.Name, "Excel Files (*.xls), *.xls", 1) If strSaveAs = "False" Then 'user cancelled the save as operation Exit Sub End If End If 'code to hide certain sheets Application.EnableEvents = False If SaveAsUI = True Then Me.SaveAs strSaveAs Else Me.Save End If Application.EnableEvents = True 'code to unhide certain sheets End Sub -- Hope that helps. Vergel Adriano "JustBreathe" wrote: First of all, thanks everyone for the invaluable help you've provided me on this message board! ...now.... code: Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) 'code to hide certain sheets Me.Save Cancel = True 'code to unhide certain sheets End Sub question: what exactly does the Cancel = True do (or not do)? I found that when I was trying to Save As a new filename that I was not being given the option to change the name. when I comment out the Cancel = True line in my BeforeSave procedure I am able to do save with a new filename when doing a Save As. I want to understand why this is so, so that I know that I am not inadvertently causing other potential problems by removing the Cancel = True. TIA Tanya (vba newbie) |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cancel = True
Apparently I haven't got something quiet right with my procedure here. It is
saving and performing as intended when doing a Save and a Save As, but is not actually saving the workbook when going by way of Close/Yes. Code below. This is starting to get to complicated for my green mind to pinpoint. TIA for any clue!!! Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) 'Set to run each time the workbook is saved 'Userform is displayed, requiring user to provide 'percent complete and if action items are open. 'Also runs CloseHideSheets procedure so that only '"Sheet1" with the Macro Enable reminder is 'is visible the next time the workbook is opened 'if it is opened without macros enabled ThisWorkbook.Sheets("Analysis").Activate UserForm1.Show Dim strSaveAs As String Cancel = True If SaveAsUI = True Then 'get a save as filename from the user strSaveAs = Application.GetSaveAsFilename(Me.Name, "Excel Files(*.xls), *.xls", 1) If strSaveAs = "False" Then 'user cancelled the save as operation Exit Sub End If End If On Error GoTo Event_Exit Application.ScreenUpdating = False Application.EnableEvents = False CloseHideSheets If SaveAsUI = True Then Me.SaveAs strSaveAs Else Me.Save End If Application.EnableEvents = True OpenUnhideSheets ThisWorkbook.Sheets("Analysis").Activate Event_Exit: Application.ScreenUpdating = True Application.EnableEvents = True End Sub Private Sub Workbook_BeforeClose(Cancel As Boolean) 'Set to run each time the workbook is closed 'It allows the user to go back to the working 'tabs if they choose to cancel the close On Error GoTo Event_Exit Application.ScreenUpdating = False Dim Msg As String If Me.Saved = False Then Msg = "Do you want to save the changes you made to " Msg = Msg & Me.Name & "?" Ans = MsgBox(Msg, vbQuestion + vbYesNoCancel) Select Case Ans Case vbYes Call CloseHideSheets Me.Save Case vbCancel Cancel = True Exit Sub End Select End If Me.Saved = True Event_Exit: Application.ScreenUpdating = True End Sub "JustBreathe" wrote: Awesome! I tested to see if what you said was happening was and you were right. Then I was able fit your suggested code in and it is worked just right on the first try. ....I'm starting to scare myself that I'm actually able to decipher and apply the advice you folks are able to provide :P Tanya (newbie) "Vergel Adriano" wrote: Hello Tanya, Cancel=True in the BeforeSave event means cancel the save operation. If Cancel=False, Excel will proceed with saving your file at the end of the event. You need to have Cancel=True because you had already saved the workbook on the previous line and there's no need for Excel to save it again. Furthermore, if you take that line out, Excel will end up saving your workbook after you unhide the sheets. Try your BeforeSave code like this: Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) Dim strSaveAs As String Cancel = True If SaveAsUI = True Then 'get a save as filename from the user strSaveAs = Application.GetSaveAsFilename(Me.Name, "Excel Files (*.xls), *.xls", 1) If strSaveAs = "False" Then 'user cancelled the save as operation Exit Sub End If End If 'code to hide certain sheets Application.EnableEvents = False If SaveAsUI = True Then Me.SaveAs strSaveAs Else Me.Save End If Application.EnableEvents = True 'code to unhide certain sheets End Sub -- Hope that helps. Vergel Adriano "JustBreathe" wrote: First of all, thanks everyone for the invaluable help you've provided me on this message board! ...now.... code: Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) 'code to hide certain sheets Me.Save Cancel = True 'code to unhide certain sheets End Sub question: what exactly does the Cancel = True do (or not do)? I found that when I was trying to Save As a new filename that I was not being given the option to change the name. when I comment out the Cancel = True line in my BeforeSave procedure I am able to do save with a new filename when doing a Save As. I want to understand why this is so, so that I know that I am not inadvertently causing other potential problems by removing the Cancel = True. TIA Tanya (vba newbie) |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cancel = True
Apparently I haven't got something quiet right with my procedure here. It is
saving and performing as intended when doing a Save and a Save As, but is not actually saving the workbook when going by way of Close/Yes. Code below. This is starting to get to complicated for my green mind to pinpoint. TIA for any clue!!! Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) 'Set to run each time the workbook is saved 'Userform is displayed, requiring user to provide 'percent complete and if action items are open. 'Also runs CloseHideSheets procedure so that only '"Sheet1" with the Macro Enable reminder is 'is visible the next time the workbook is opened 'if it is opened without macros enabled ThisWorkbook.Sheets("Analysis").Activate UserForm1.Show Dim strSaveAs As String Cancel = True If SaveAsUI = True Then 'get a save as filename from the user strSaveAs = Application.GetSaveAsFilename(Me.Name, "Excel Files(*.xls), *.xls", 1) If strSaveAs = "False" Then 'user cancelled the save as operation Exit Sub End If End If On Error GoTo Event_Exit Application.ScreenUpdating = False Application.EnableEvents = False CloseHideSheets If SaveAsUI = True Then Me.SaveAs strSaveAs Else Me.Save End If Application.EnableEvents = True OpenUnhideSheets ThisWorkbook.Sheets("Analysis").Activate Event_Exit: Application.ScreenUpdating = True Application.EnableEvents = True End Sub Private Sub Workbook_BeforeClose(Cancel As Boolean) 'Set to run each time the workbook is closed 'It allows the user to go back to the working 'tabs if they choose to cancel the close On Error GoTo Event_Exit Application.ScreenUpdating = False Dim Msg As String If Me.Saved = False Then Msg = "Do you want to save the changes you made to " Msg = Msg & Me.Name & "?" Ans = MsgBox(Msg, vbQuestion + vbYesNoCancel) Select Case Ans Case vbYes Call CloseHideSheets Me.Save Case vbCancel Cancel = True Exit Sub End Select End If Me.Saved = True Event_Exit: Application.ScreenUpdating = True End Sub "JustBreathe" wrote: First of all, thanks everyone for the invaluable help you've provided me on this message board! ...now.... code: Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) 'code to hide certain sheets Me.Save Cancel = True 'code to unhide certain sheets End Sub question: what exactly does the Cancel = True do (or not do)? I found that when I was trying to Save As a new filename that I was not being given the option to change the name. when I comment out the Cancel = True line in my BeforeSave procedure I am able to do save with a new filename when doing a Save As. I want to understand why this is so, so that I know that I am not inadvertently causing other potential problems by removing the Cancel = True. TIA Tanya (vba newbie) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
why doesnt cancel=true always work? | Excel Programming | |||
Another way to suppress print besides Cancel = True? | Excel Programming | |||
BeforeDoubleClick Cancel=True not working | Excel Programming | |||
Close workbook with "Cancel=TRUE" in the BeforeClose()" | Excel Programming | |||
Setting Cancel = True in WorkbookBeforePrint | Excel Programming |