PDA

View Full Version : Sleeper: Problem with Before_Close code



Stead
07-02-2005, 02:50 PM
Hi, i've been having a lot of trouble trying to get my code that i've written that will work fine in a sub on its own, but the same code when used in the before_close bit refuses to run.

most annoyingly i've noticed that once its reached the code it refuses to run it will also cancel out of the before_close code, (brings up an error, and tells me i am unable to debug, my only choice is to click contunie, which brings up the error again, or click end) which is a bit annoying as i want it to do stuff then save, so the user doesn't ahve to click save.

things i've been trying to do in the before close
use appactivate & send keys to send a command to a vb app
move certain sheets into a new workbook and save them

if anyone wishes to help i will be very grateful, these things aren't essential, at least not yet, but very annoying if they cannot be done!

sorry if make no sence, been trying to work it out most of the day :banghead:

Thanks.

Bob Phillips
07-02-2005, 03:22 PM
Hi, i've been having a lot of trouble trying to get my code that i've written that will work fine in a sub on its own, but the same code when used in the before_close bit refuses to run.

most annoyingly i've noticed that once its reached the code it refuses to run it will also cancel out of the before_close code, (brings up an error, and tells me i am unable to debug, my only choice is to click contunie, which brings up the error again, or click end) which is a bit annoying as i want it to do stuff then save, so the user doesn't ahve to click save.

things i've been trying to do in the before close
use appactivate & send keys to send a command to a vb app
move certain sheets into a new workbook and save them

if anyone wishes to help i will be very grateful, these things aren't essential, at least not yet, but very annoying if they cannot be done!

sorry if make no sence, been trying to work it out most of the day :banghead:

Thanks.

Some code and details of what you are doint/trying to do?

Stead
07-02-2005, 04:52 PM
Sorry about the last post! i'll try and be clearer this time

i have the following code


Public Sub UnLoadProducts()
ClearFormula
Sheets(Array("Products", "Accessories", "Price Structure")).Select
Sheets("Accessories").Activate
Sheets(Array("Products", "Accessories", "Price Structure")).Move
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:= _
MePath & "\Data\New Products.xls" _
, FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
Application.DisplayAlerts = True
ActiveWindow.Close
End Sub


So that moves over 3 sheets, then saves them into a seperate book.
i'm not sure if theres a better way, i'm convinced there is, as i've done it before, however i was clever enough to only have a copy on my pendrive, which was a bit faulty (as i discovered later when i tried to open up it up for reference)

Anyway my problem is that it runs fine when called up at normally, however when its in the before close section, exactly how its written below

Private Sub Workbook_BeforeClose(Cancel As Boolean)
UnLoadProducts
Workbooks("Spreadsheet.xls").Save
End Sub

it refuses to run

What i can't understand is, i know i've done this before, maybe not using the same code, but i done it and i never had any wierd errors in the beforeclose code, at least if i did, i managed to get it working :banghead:

When debugging on the before_close event i get this error appear on the line

Sheets(Array("Products", "Accessories", "Price Structure")).Move

The error is 'Can't enter break mode at this time' my options are continue or end, it then stops the code, regardless of if i select end or continue.

The odd thing is, if i do'nt put it into debug mode, it seems to save the file and close it, but the code stops running at some point as it prompts me to save the file even tho the last command is to save

Norie
07-02-2005, 05:49 PM
Stead

What are you actually trying to do?

In the 1st line of your code you select 3 sheets, you then activate a single sheet.

You then Move the 3 sheets selected earlier to (I believe) a new workbook.

What do ClearFormula, UnLoadProducts and MePath refer to ?

Stead
07-03-2005, 03:19 AM
well,the reason the single sheet is activated, is because i just copied hte code straight from the macro recorder.

the ClearFormula links to code that blanks off the formula's in the workbook to save a bit of space, as its quite large, trims the size by about 30%, then when the workbook gets opened it creates the formula based upon a lookup table i've used which can change in size.

the UnLoadProducts is the first section of code, i just like to keep things seperate as i find it easier to run through thats how i know it works, it works fine when i call up the UnLoadProducts but when its called from the Before_Close on the workbook i get a wierd error.

MePath refers to the path of the workbook,

What i want to do is export those 3 sheets into a new workbook when the workbook is closed.

Basically
User Clicks Close on SpreadSheet
Before_Close Code Runs
'Unloads' those 3 sheets by copying them to a new book then saving them
saves the workbook
end of before_close event

I'm not sure if theres an export sheet command, i always thought there was?

Norie
07-03-2005, 07:17 AM
Where are you assigning a value to MePath?

Stead
07-04-2005, 03:18 AM
When the workbook is opened i assign the MePath, althogh if that isn't set a error will pop up and allow me to debug to put it in the intermediate window( i think thats what its called)

I haven't actually tried hardcoding the path to see if it is that causing the problem, i wouldn't of thought it would be, i'll try in a minute and see if it does change anything.

Thanks!

Stead
07-04-2005, 04:23 AM
I've changed it now too



Private Sub Workbook_BeforeClose(Cancel As Boolean)
ClearFormula
'On Error GoTo noPriceS
Sheets(Array("Products", "Accessories", "Price Structure")).Select
Application.DisplayAlerts = False
ActiveWindow.SelectedSheets.Delete
Application.DisplayAlerts = True
Save
'noPriceS:
End Sub


I've commited out the On Error bit, as that was just during testing if i click no when it asks me to save then when i goto close it it tries to delete those sheets again, but of course they are already gone.

it deletes the sheets, but it does not save, it asks me everytime if i wish to save, it just seems to be ignoring the save command?

The Following I changed and added too message boxes, one saying before save, one after, both appear, however it still asks me to save changes when i close the program



Private Sub Workbook_BeforeClose(Cancel As Boolean)
ClearFormula
On Error GoTo noPriceS
Sheets(Array("Products", "Accessories", "Price Structure")).Select
Application.DisplayAlerts = False
ActiveWindow.SelectedSheets.Delete
Application.DisplayAlerts = True
Main.Activate
MsgBox "Not Saved Yet"
Save
MsgBox "Saved"
noPriceS:
End Sub


i have also tried the workbooks("Name").save same problem
as far as i can tell (ie a new workbook being created called new products being made when i delete the old one, what i did before did work, but for the some reason it wouldn't work when i tried stepping through the code, and it didn't save, so i thought if i took out the bit that saves those sheets toa new book the save would work, but it just seems to ignore it?

****
Edit
****

After a bit more testing i've discovered it does actually run through all the code in the before_close event, however it still asks me to save even tho i have saved it in that code.

I do have the titlebar changing as you select different sheets, so i tested to see if that was causing a change for it to save again after i save it, so i took out that code, and yet the save dialog still appears?

Because that didn't seem to help, i took out the application.displayalerts = true, as far as i'm concered having the line application.displayalerts = false should prevent any dialogs being shown? but it still asks to save?

Complety stumped on this, i know i'm no advanced VBA programmer by my standards, but i just don't understand why it does this, also i don't understand why it won't let me debug the before_close event without giving me a wierd error.

Also other workbooks close fine without any prompt or wierd debugging error if put the save command in the before_close, could it be jus a wierd error in this book? i may trying copying everything over to a new book later, but at the moment i need a brake!

I Appricate that you've been following this, i'll post later to see if making a new workbook solves it, i know in the past i've had wierd errors that seem to disappear by doing that, just theres a lot of stuff in this project so far and its gonna take a while to move it all over :rotlaugh:

Norie
07-04-2005, 06:48 AM
I'm getting more confused.

What's Main and is Save another piece of code?

Stead
07-04-2005, 03:09 PM
Save just saves the workbook

Main.Activate is setting the first sheet which is called main, since its the main sheet the user deals with!

mdmackillop
07-04-2005, 04:13 PM
Hi,
Try the following (adjusted as required)


Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.EnableEvents = False
Application.DisplayAlerts = False
MePath = "C:\AAA"
On Error GoTo ErrH
Sheets(Array("Products", "Accessories", "Price Structure")).Move
ActiveWorkbook.SaveAs Filename:=MePath & "\Data\New Products.xls"
ActiveWindow.Close
ErrH:
Application.DisplayAlerts = True
Application.EnableEvents = True
ActiveWorkbook.Close True
End Sub

Stead
07-04-2005, 04:46 PM
I thought that was gonna work then!, works fine, Except for the fact i get 2 or 3 error boxes about excel.exe trying to reference memory address, then trying to write to a memory address

but if i remove the ActiveWorkbook.Close True, it'll move over the sheets, save them, but still pops up asking the user to save

also after all those crashes, the sheets do not get moved out, merely copied over to a new book and saved, doesn't seem to make any sence to me, i'm thinking i should just leave it and let the user click yes when it asks to save everytime (even tho if i put a save command it it does actually save the workbook)

...

what if i save it, put in cancel = true, then use the application.quit ? can i force excel to shutdown without getting it to save any changes to open workbooks? (this sheet workbook will be the only one opened, its launched from an app so its has its own little excel userspace!:))