duplicate results for code

[mleosu]

I have a beforesave event that checks to see if any changes were made to the spreadsheet, to check for errors, and to save and send an email.

For some reason - the part of the code that is for saving and emailing is running twice - so 2 message boxes are shown and 2 emails are sent.

I dont understand why this is running twice. Any help is appreciated.

(if you need the whole code let me know - but here is just the part that is running twice)

'if no errors - then save
If a = 0 And b > 0 Then '(a is to count errors)  (b is to count changes)
ans = MsgBox("ALL INFORMATION IS PROVIDED - Spreadsheet will now SAVE and EMAIL to Minnie.Walker@brinksinc.com", vbwarning + vbOKOnly)
If ans = vbOK Then
    Save
    Call Sheet1.SendMail
End If
End If

 

[SkipVought]

Hi,

Follow this now...

this bit of code is done by the, "...beforesave event..."

So SOMEONE has ALREADY called for a SAVE.

Then, in your code, you SAVE, calling the beforesave event again. Sound to me like a recursive call that has been mercifully aborted.

Do you really want this run in the beforesave event?

Skip,

_[red][/red]

 

[mleosu]

To save space here - I have uploaded my code in documents to the web for viewing

This is the before save macro that calls the errorcheck macro and the sendmail macro:

http://docs.google.com/Doc?id=ddp638t3_2hmsqx3

This is the ErrorCheck Macro:

http://docs.google.com/Doc?id=ddp638t3_4ftjq4w

This is the send mail macro:

http://docs.google.com/Doc?id=ddp638t3_3hprv83

I could only find where I called the save and the sendmail once. Am I missing something? Is it being called anyway?

 

[PHV]

What about this ?
...
If ans = vbOK Then
Application.EnableEvents = False
Save
Call Sheet1.SendMail
Application.EnableEvents = True
End If
...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[mleosu]

works - thanks.
i need to read more about Application.EnableEvents!

 

[SkipVought]

What is firing the BeforeSave event?

Skip,

_[red][/red]

 

[mleosu]

when you hit save on the menu bar (or ctrl+s - however you save normally.

 

[stevexff]

i need to read more about Application.EnableEvents!

No, I think you need to understand more about events. The whole purpose of the BeforeSaveEvent is to allow you to get control when the user saves the workbook. In this case, you do some validation and optionally email it if it passes muster. You don't have to explicitly save it yourself, that will happen anyway unless you set the Cancel parameter to prevent it. Try just removing the Save from your original post, then you won't have a recursion problem...

Steve

[small]"Every program can be reduced by one instruction, and every program has at least one bug. Therefore, any program can be reduced to one instruction which doesn't work." (Object:erlDesignPatterns)[/small]

 

[SkipVought]

Is what steve says not what I have been trying to get you to understand?


The ONLY way that the beforesave event fires, if because someone has issued a SAVE command SOMEWHERE. So BEFORE your application manager saves the file (remember, the SAVE COMMAND has ALREADY been issued), DO THIS STUFF.

Think about it!!!

Skip,

_[red][/red]

 

[mleosu]

ok - i get it. thanks