Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Prompted to save even after setting wb.saved=true
I would like to avoid being prompted to save the workbook that I am closing
twice. The problem is this... I have an add-in that I created that must must execute some custom when any workbook saves. So in my add-in I handle the Application_WorkbookBeforeSave. The code looks like this: -------------------------------------------------------- Private giInSave as boolean Private Sub moApps_WorkbookBeforeSave(ByVal Wb As Workbook, ByVal SaveAsUI As Boolean, Cancel As Boolean) If Not Wb.IsAddin Then 'if already in save then quit this sub If giInSave Then Exit Sub End If 'if workbook is not saved then If Not Wb.Saved Then If MsgBox("Would you like to save the formulas without saving the calculated data for security purposes?", vbYesNo) = vbYes Then giInSave = True moApps.Calculate 'recalculate all cells in the app so that Finance function returns 0 everywhere Wb.Save 'save the worksheet giInSave = False moApps.Calculate 'recalculate the cells to put the numbers back Wb.Saved = True 'mark the workbook as saved Cancel = True 'do not execute the save command which normally would follow this sub because it was done 4 lines up End If End If End If End Sub -------------------------------------------------------- This code works perfectly when I save the workbook from toolbar or menus or keyboard shortcut. It even works properly when I close the Excel application. However, when I close the workbook...the Application_WorkbookBeforeClose event is raised (I do not do much in there) and immediately after the End Sub, I am prompted to Save (I guess this is from the actual Close event, and if I say yes, then the above event is raised (WorkbookBeforeSave), which is good. It runs through, but then immediately after the End Sub I guess the Close event is called again, and I am reprompted to save!?!?!?!? Even though I just finished setting the wb.saved=true!?!?!?!?!? Any ideas? Thanks. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Prompted to save even after setting wb.saved=true
Once you click on close the workbook and
excel dialogue box appears, all the code that is excecuted is AFTER workbook_close (not before). and then setting Wb.Saved=True does not have effect. So seems like you need to write code in BeforeClose event too. In Thisworkbook class in General, in addition to defining giInSave, you can add another variable say giInClose as boolean. Then is the beforeclose event add a single line giInClose = True In your BeforeSave event, just after Wb.Save 'save the worksheet (workbook) line add below code If giInclose Then giInClose = False Exit Sub Exit Sub This should solve it I think, Try it. Sharad "JE" wrote in message ... I would like to avoid being prompted to save the workbook that I am closing twice. The problem is this... I have an add-in that I created that must must execute some custom when any workbook saves. So in my add-in I handle the Application_WorkbookBeforeSave. The code looks like this: -------------------------------------------------------- Private giInSave as boolean Private Sub moApps_WorkbookBeforeSave(ByVal Wb As Workbook, ByVal SaveAsUI As Boolean, Cancel As Boolean) If Not Wb.IsAddin Then 'if already in save then quit this sub If giInSave Then Exit Sub End If 'if workbook is not saved then If Not Wb.Saved Then If MsgBox("Would you like to save the formulas without saving the calculated data for security purposes?", vbYesNo) = vbYes Then giInSave = True moApps.Calculate 'recalculate all cells in the app so that Finance function returns 0 everywhere Wb.Save 'save the worksheet giInSave = False moApps.Calculate 'recalculate the cells to put the numbers back Wb.Saved = True 'mark the workbook as saved Cancel = True 'do not execute the save command which normally would follow this sub because it was done 4 lines up End If End If End If End Sub -------------------------------------------------------- This code works perfectly when I save the workbook from toolbar or menus or keyboard shortcut. It even works properly when I close the Excel application. However, when I close the workbook...the Application_WorkbookBeforeClose event is raised (I do not do much in there) and immediately after the End Sub, I am prompted to Save (I guess this is from the actual Close event, and if I say yes, then the above event is raised (WorkbookBeforeSave), which is good. It runs through, but then immediately after the End Sub I guess the Close event is called again, and I am reprompted to save!?!?!?!? Even though I just finished setting the wb.saved=true!?!?!?!?!? Any ideas? Thanks. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Prompted to save even after setting wb.saved=true
Just realized that, when you click on Close the workbook and
it asks "Do you want to Save Changes?", if you click on Yes, it is fine. But if you click on No or Cancel then giInClose will never get to set to False again. Therefore BeforeClose event (and in this and previous post of mine, I meant this event should be in the Add in mspApps_WorkbookBeforeClose) as under: (The code I have in mind that you cancel the normal close method and do same as normal save method our own way) Private Sub moApps_WorkbookBeforeSave(ByVal Wb As Workbook, Cancel As Boolean) Cancel = True Select Case MsgBox("Do you want to save changes in " & Wb.Name & "?", vbYesNoCancel) Case vbNo Wb.Saved = True Wb.Close Case vbYes giInClose = True Wb.Save End Select End Sub i.e. you set giInClose = True only in case user clicks on "Yes" If he clicks on cancel, then Cancel = True is already said in the first line so no need to add Case vbCancel Sharad "Sharad Naik" wrote in message ... Once you click on close the workbook and excel dialogue box appears, all the code that is excecuted is AFTER workbook_close (not before). and then setting Wb.Saved=True does not have effect. So seems like you need to write code in BeforeClose event too. In Thisworkbook class in General, in addition to defining giInSave, you can add another variable say giInClose as boolean. Then is the beforeclose event add a single line giInClose = True In your BeforeSave event, just after Wb.Save 'save the worksheet (workbook) line add below code If giInclose Then giInClose = False Exit Sub Exit Sub This should solve it I think, Try it. Sharad "JE" wrote in message ... I would like to avoid being prompted to save the workbook that I am closing twice. The problem is this... I have an add-in that I created that must must execute some custom when any workbook saves. So in my add-in I handle the Application_WorkbookBeforeSave. The code looks like this: -------------------------------------------------------- Private giInSave as boolean Private Sub moApps_WorkbookBeforeSave(ByVal Wb As Workbook, ByVal SaveAsUI As Boolean, Cancel As Boolean) If Not Wb.IsAddin Then 'if already in save then quit this sub If giInSave Then Exit Sub End If 'if workbook is not saved then If Not Wb.Saved Then If MsgBox("Would you like to save the formulas without saving the calculated data for security purposes?", vbYesNo) = vbYes Then giInSave = True moApps.Calculate 'recalculate all cells in the app so that Finance function returns 0 everywhere Wb.Save 'save the worksheet giInSave = False moApps.Calculate 'recalculate the cells to put the numbers back Wb.Saved = True 'mark the workbook as saved Cancel = True 'do not execute the save command which normally would follow this sub because it was done 4 lines up End If End If End If End Sub -------------------------------------------------------- This code works perfectly when I save the workbook from toolbar or menus or keyboard shortcut. It even works properly when I close the Excel application. However, when I close the workbook...the Application_WorkbookBeforeClose event is raised (I do not do much in there) and immediately after the End Sub, I am prompted to Save (I guess this is from the actual Close event, and if I say yes, then the above event is raised (WorkbookBeforeSave), which is good. It runs through, but then immediately after the End Sub I guess the Close event is called again, and I am reprompted to save!?!?!?!? Even though I just finished setting the wb.saved=true!?!?!?!?!? Any ideas? Thanks. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Prompted to save even after setting wb.saved=true
You got another reply to your other post.
JE wrote: I would like to avoid being prompted to save the workbook that I am closing twice. The problem is this... I have an add-in that I created that must must execute some custom when any workbook saves. So in my add-in I handle the Application_WorkbookBeforeSave. The code looks like this: -------------------------------------------------------- Private giInSave as boolean Private Sub moApps_WorkbookBeforeSave(ByVal Wb As Workbook, ByVal SaveAsUI As Boolean, Cancel As Boolean) If Not Wb.IsAddin Then 'if already in save then quit this sub If giInSave Then Exit Sub End If 'if workbook is not saved then If Not Wb.Saved Then If MsgBox("Would you like to save the formulas without saving the calculated data for security purposes?", vbYesNo) = vbYes Then giInSave = True moApps.Calculate 'recalculate all cells in the app so that Finance function returns 0 everywhere Wb.Save 'save the worksheet giInSave = False moApps.Calculate 'recalculate the cells to put the numbers back Wb.Saved = True 'mark the workbook as saved Cancel = True 'do not execute the save command which normally would follow this sub because it was done 4 lines up End If End If End If End Sub -------------------------------------------------------- This code works perfectly when I save the workbook from toolbar or menus or keyboard shortcut. It even works properly when I close the Excel application. However, when I close the workbook...the Application_WorkbookBeforeClose event is raised (I do not do much in there) and immediately after the End Sub, I am prompted to Save (I guess this is from the actual Close event, and if I say yes, then the above event is raised (WorkbookBeforeSave), which is good. It runs through, but then immediately after the End Sub I guess the Close event is called again, and I am reprompted to save!?!?!?!? Even though I just finished setting the wb.saved=true!?!?!?!?!? Any ideas? Thanks. -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Prompted to save | Excel Discussion (Misc queries) | |||
Prompted to save even after setting wb.Saved=true | Excel Programming | |||
Prompted to save even after setting wb.Saved=true | Excel Programming | |||
Will setting Saved = True in Workbook_Open have a negative effect? | Excel Programming | |||
Save as CSV without being prompted | Excel Programming |