Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Workbook.close & beforeclose
Hello,
I’ve searched the groups, but can’t seem to find anything that quite addresses this, hoping someone can tell me where I’m going wrong. I have the following code in the BeforeClose event : Private Sub Workbook_BeforeClose(Cancel As Boolean) Dim wk As Worksheet For Each wk In ThisWorkbook.Worksheets wk.Visible = xlSheetVisible Next wk End Sub When I close the workbook manually, it works as expected. But if I run “ThisWorkbook.Close savechanges:=False” from a procedure, it doesn’t. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Workbook.close & beforeclose
Hi,
It looks like Excel is doing exactly what your telling it to do. In your 'other' sub your saying close without saving the changes. the workbook before close event executes and unhides all your sheets but then these changes aren't saved so the workbook stays in the state it was before the code executed. Mike "Dave Unger" wrote: Hello, Ive searched the groups, but cant seem to find anything that quite addresses this, hoping someone can tell me where Im going wrong. I have the following code in the BeforeClose event : Private Sub Workbook_BeforeClose(Cancel As Boolean) Dim wk As Worksheet For Each wk In ThisWorkbook.Worksheets wk.Visible = xlSheetVisible Next wk End Sub When I close the workbook manually, it works as expected. But if I run €śThisWorkbook.Close savechanges:=False€ť from a procedure, it doesnt. I can step thru the code manually, but nothing happens. Im getting the same results with XL97 & 2007, so obviously Im missing something basic here. Any help would be very much appreciated. regards Dave U |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Workbook.close & beforeclose
Hi Mike,
Thanks for your reply. I realize my workbook isn't getting saved, but that's not the issue here. Let's add a Stop to the end of the beforeclose event: Private Sub Workbook_BeforeClose(Cancel As Boolean) Dim wk As Worksheet For Each wk In ThisWorkbook.Worksheets wk.Visible = xlSheetVisible Next wk STOP End Sub Now, when I close the workbook manually, when the code hits the Stop, the sheets are visible. When I run “ThisWorkbook.Close savechanges:=False” (or true) from a procedure, the sheets are NOT visible at the stop. I've played around with this for couple of days, and searched the Internet, so far no luck. On Jan 29, 3:13*am, Mike H wrote: Hi, It looks like Excel is doing exactly what your telling it to do. In your 'other' sub your saying close without saving the changes. the workbook before close event executes and unhides all your sheets but then these changes aren't saved so the workbook stays in the state it was before the code executed. Mike |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
'BeforeClose' code problems:book won't close if more than one book is open | Excel Programming | |||
Need code to close minimized workbooks in BeforeClose event | Excel Programming | |||
Close workbook with "Cancel=TRUE" in the BeforeClose()" | Excel Programming | |||
Workbook BeforeClose Bug | Excel Programming | |||
File won't close when "beforeclose" event is used. | Excel Programming |