Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Before "Before Close" help needed!
Hi
Have a workbook that "before close" needs to export several of its sheets to a new workbook, create a pivot table in that workbook, close and save the new work book, then get on with closing itself. I have all the code to deal with this and it works well by itself. However I also run the first workbook from a set of buttons on the first sheet. One of these is an Exit button, which saves and closes the workbook. I have also set the Before Close event in the workbook to do the same thing, so that the user has to save and close, which ever way they try to close the workbook. My problem is that introducing the sub procedure in the "before close" event to deal with the export. For some reason, it doesn't work. What is happening in the "before close" event that is different from normal? e.g. my sub needs to unhide 4 worksheets to in order to export them. This does not happen when run from within the "before close" sub on the workbook. Hope this all makes sense? Joe |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Before "Before Close" help needed!
Hi Joe
you may paste your code currently used in this event procedure -- Regards Frank Kabel Frankfurt, Germany Joe 90 wrote: Hi Have a workbook that "before close" needs to export several of its sheets to a new workbook, create a pivot table in that workbook, close and save the new work book, then get on with closing itself. I have all the code to deal with this and it works well by itself. However I also run the first workbook from a set of buttons on the first sheet. One of these is an Exit button, which saves and closes the workbook. I have also set the Before Close event in the workbook to do the same thing, so that the user has to save and close, which ever way they try to close the workbook. My problem is that introducing the sub procedure in the "before close" event to deal with the export. For some reason, it doesn't work. What is happening in the "before close" event that is different from normal? e.g. my sub needs to unhide 4 worksheets to in order to export them. This does not happen when run from within the "before close" sub on the workbook. Hope this all makes sense? JoeHi |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Before "Before Close" help needed!
Suggestion is that you insert the statement "DoEvents" in
the sub procedure just after it unhides the worksheets; or, alternatively, at the appropriate point in the Before_Close event code. Regards, Greg (VBA amateur) -----Original Message----- Hi Have a workbook that "before close" needs to export several of its sheets to a new workbook, create a pivot table in that workbook, close and save the new work book, then get on with closing itself. I have all the code to deal with this and it works well by itself. However I also run the first workbook from a set of buttons on the first sheet. One of these is an Exit button, which saves and closes the workbook. I have also set the Before Close event in the workbook to do the same thing, so that the user has to save and close, which ever way they try to close the workbook. My problem is that introducing the sub procedure in the "before close" event to deal with the export. For some reason, it doesn't work. What is happening in the "before close" event that is different from normal? e.g. my sub needs to unhide 4 worksheets to in order to export them. This does not happen when run from within the "before close" sub on the workbook. Hope this all makes sense? Joe . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Before "Before Close" help needed!
OK
Cracked the first part, and should have remembered this! Using the EnabledEvents = False property in the click button event solves the doubling up with the Before Close event. I have also figured out how to set the BeforeClose Cancel argument to True (Cancel =True) which means BeforeClose is bypassed. Nevertheless, I now need to know how to stop the Yes/No/Cancel dialog appearing from BeforeClose when called from another procedure(e.g. Activeworkbook.Close) rather than giving me the dialog? (Application.DisplayAlerts = False, doesn't seem to work in this instance?) Cheers Joe "Joe 90" (remove silly spam) wrote in message ... Hi Have a workbook that "before close" needs to export several of its sheets to a new workbook, create a pivot table in that workbook, close and save the new work book, then get on with closing itself. I have all the code to deal with this and it works well by itself. However I also run the first workbook from a set of buttons on the first sheet. One of these is an Exit button, which saves and closes the workbook. I have also set the Before Close event in the workbook to do the same thing, so that the user has to save and close, which ever way they try to close the workbook. My problem is that introducing the sub procedure in the "before close" event to deal with the export. For some reason, it doesn't work. What is happening in the "before close" event that is different from normal? e.g. my sub needs to unhide 4 worksheets to in order to export them. This does not happen when run from within the "before close" sub on the workbook. Hope this all makes sense? Joe |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel - Golf - how to display "-2" as "2 Under" or "4"as "+4" or "4 Over" in a calculation cell | Excel Discussion (Misc queries) | |||
change "true" and "false" to "availble" and "out of stock" | Excel Worksheet Functions | |||
Count occurences of "1"/"0" (or"TRUE"/"FALSE") in a row w. conditions in the next | New Users to Excel | |||
Disable "Save" &/or "Close" | Excel Programming | |||
How can I get "File Close" to prompt me to "Save Changes" ?? | Excel Programming |