I am providing templates for the employees in the office.
Each of their files(which acts as a template for them to continually use,
example jmaytimesheet.xls it contains:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim Ans As Integer
Ans = MsgBox("Is your Timesheet complete and ready" & vbLf & "for
Submission to the Payroll Office?" & vbLf & "Answering No will Save it for
Further" & vbLf & "Input the Next time you return", vbYesNoCancel, "Your
Timesheet Status")
If Ans = vbYes Then
SendToProcessing
If Ans = vbNo Then ThisWorkbook.Save
End If
End Sub

and

Sub SendToProcessing()
Dim PEDate As String
Dim ECode As String
Dim SFolder As String
PEDate = Format(Range("H2"), "mmddyyyy") ' H2 = 5/28/04 as example
ECode = Range("C4").Value 'C4 = MAYJ as example
ChDir "C:\My Documents\Paymaster\PRBackUp\"
ActiveWorkbook.SaveCopyAs PEDate & ECode & ".xls"
End Sub

All files contained in C:\My Documents\Paymaster\PRBackUp \are to be
e-mailed to
the person that preapres the Payroll every two weeks. When the Payroll
person opens
each file and Prints it out for their records << which is OK But when
they try and close
each one the Workbook_BeforeClose macro runs.

How can I remove this from the files that have been Saved as 052804MAYJ.xls
so that
this situation doesn't occur? Any other advice you see let me know?
Tks in Advance,

Hi
have a look at Ron's side. His add-in also removes code from the
sheets:
http://www.rondebruin.nl/sendmail.htm

--
Regards
Frank Kabel
Frankfurt, Germany


JMay wrote:
I am providing templates for the employees in the office.
Each of their files(which acts as a template for them to continually
use, example jmaytimesheet.xls it contains:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim Ans As Integer
Ans = MsgBox("Is your Timesheet complete and ready" & vbLf & "for
Submission to the Payroll Office?" & vbLf & "Answering No will Save
it for Further" & vbLf & "Input the Next time you return",
vbYesNoCancel, "Your Timesheet Status")
If Ans = vbYes Then
SendToProcessing
If Ans = vbNo Then ThisWorkbook.Save
End If
End Sub

and

Sub SendToProcessing()
Dim PEDate As String
Dim ECode As String
Dim SFolder As String
PEDate = Format(Range("H2"), "mmddyyyy") ' H2 = 5/28/04 as
example ECode = Range("C4").Value 'C4 = MAYJ as example
ChDir "C:\My Documents\Paymaster\PRBackUp\"
ActiveWorkbook.SaveCopyAs PEDate & ECode & ".xls"
End Sub

All files contained in C:\My Documents\Paymaster\PRBackUp \are to be
e-mailed to
the person that preapres the Payroll every two weeks. When the
Payroll person opens
each file and Prints it out for their records << which is OK But
when they try and close
each one the Workbook_BeforeClose macro runs.

How can I remove this from the files that have been Saved as
052804MAYJ.xls so that
this situation doesn't occur? Any other advice you see let me know?
Tks in Advance,

Just visited below referenced site, scrolled-down and didn't see
specifically
what you suggested I should see.. where and how do I see how he is
"removing code" from the workbooks, or add-in workbook (Is it available to
be opened?, that is not password protected)?
Thanks for your help Frank...


"Frank Kabel" wrote in message
...
Hi
have a look at Ron's side. His add-in also removes code from the
sheets:
http://www.rondebruin.nl/sendmail.htm

--
Regards
Frank Kabel
Frankfurt, Germany


JMay wrote:
I am providing templates for the employees in the office.
Each of their files(which acts as a template for them to continually
use, example jmaytimesheet.xls it contains:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim Ans As Integer
Ans = MsgBox("Is your Timesheet complete and ready" & vbLf & "for
Submission to the Payroll Office?" & vbLf & "Answering No will Save
it for Further" & vbLf & "Input the Next time you return",
vbYesNoCancel, "Your Timesheet Status")
If Ans = vbYes Then
SendToProcessing
If Ans = vbNo Then ThisWorkbook.Save
End If
End Sub

and

Sub SendToProcessing()
Dim PEDate As String
Dim ECode As String
Dim SFolder As String
PEDate = Format(Range("H2"), "mmddyyyy") ' H2 = 5/28/04 as
example ECode = Range("C4").Value 'C4 = MAYJ as example
ChDir "C:\My Documents\Paymaster\PRBackUp\"
ActiveWorkbook.SaveCopyAs PEDate & ECode & ".xls"
End Sub

All files contained in C:\My Documents\Paymaster\PRBackUp \are to be
e-mailed to
the person that preapres the Payroll every two weeks. When the
Payroll person opens
each file and Prints it out for their records << which is OK But
when they try and close
each one the Workbook_BeforeClose macro runs.

How can I remove this from the files that have been Saved as
052804MAYJ.xls so that
this situation doesn't occur? Any other advice you see let me know?
Tks in Advance,

Hi
sorry. As I use Ron's add-in and we had a private discussion about
removing the code prior to mailing I assumed that this code snippet was
also available on his site. So either Ron will post some example code
or have a look at the following site for the genral principles of
deleting code programmatically:
http://www.cpearson.com/excel/vbe.htm

--
Regards
Frank Kabel
Frankfurt, Germany


JMay wrote:
Just visited below referenced site, scrolled-down and didn't see
specifically
what you suggested I should see.. where and how do I see how he is
"removing code" from the workbooks, or add-in workbook (Is it
available to be opened?, that is not password protected)?
Thanks for your help Frank...


"Frank Kabel" wrote in message
...
Hi
have a look at Ron's side. His add-in also removes code from the
sheets:
http://www.rondebruin.nl/sendmail.htm

--
Regards
Frank Kabel
Frankfurt, Germany


JMay wrote:
I am providing templates for the employees in the office.
Each of their files(which acts as a template for them to
continually
use, example jmaytimesheet.xls it contains:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim Ans As Integer
Ans = MsgBox("Is your Timesheet complete and ready" & vbLf &
"for Submission to the Payroll Office?" & vbLf & "Answering No will
Save it for Further" & vbLf & "Input the Next time you return",
vbYesNoCancel, "Your Timesheet Status")
If Ans = vbYes Then
SendToProcessing
If Ans = vbNo Then ThisWorkbook.Save
End If
End Sub

and

Sub SendToProcessing()
Dim PEDate As String
Dim ECode As String
Dim SFolder As String
PEDate = Format(Range("H2"), "mmddyyyy") ' H2 = 5/28/04
as
example ECode = Range("C4").Value 'C4 = MAYJ as example
ChDir "C:\My Documents\Paymaster\PRBackUp\"
ActiveWorkbook.SaveCopyAs PEDate & ECode & ".xls"
End Sub

All files contained in C:\My Documents\Paymaster\PRBackUp \are to
be
e-mailed to
the person that preapres the Payroll every two weeks. When the
Payroll person opens
each file and Prints it out for their records << which is OK But
when they try and close
each one the Workbook_BeforeClose macro runs.

How can I remove this from the files that have been Saved as
052804MAYJ.xls so that
this situation doesn't occur? Any other advice you see let me
know?
Tks in Advance,

I don't think you would have much success in having the BeforeClose event
remove itself. Easier might be to have the before close event check
something unique about the payroll processing site and not run if it detects
it is at payroll processing. this could be doing a dir command on a
directory that would only be visible from payroll processing or something
like that. At the top of Beforeclose, just add

if uniquecondition then exit sub

An alternative would be to have payroll open the file with macros disabled
or open it with macros and have the macros disable events.


--
Regards,
Tom Ogilvy

"JMay" wrote in message news:A9Kuc.9307$Tw.8072@lakeread06...
Just visited below referenced site, scrolled-down and didn't see
specifically
what you suggested I should see.. where and how do I see how he is
"removing code" from the workbooks, or add-in workbook (Is it available to
be opened?, that is not password protected)?
Thanks for your help Frank...


"Frank Kabel" wrote in message
...
Hi
have a look at Ron's side. His add-in also removes code from the
sheets:
http://www.rondebruin.nl/sendmail.htm

--
Regards
Frank Kabel
Frankfurt, Germany


JMay wrote:
I am providing templates for the employees in the office.
Each of their files(which acts as a template for them to continually
use, example jmaytimesheet.xls it contains:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim Ans As Integer
Ans = MsgBox("Is your Timesheet complete and ready" & vbLf & "for
Submission to the Payroll Office?" & vbLf & "Answering No will Save
it for Further" & vbLf & "Input the Next time you return",
vbYesNoCancel, "Your Timesheet Status")
If Ans = vbYes Then
SendToProcessing
If Ans = vbNo Then ThisWorkbook.Save
End If
End Sub

and

Sub SendToProcessing()
Dim PEDate As String
Dim ECode As String
Dim SFolder As String
PEDate = Format(Range("H2"), "mmddyyyy") ' H2 = 5/28/04 as
example ECode = Range("C4").Value 'C4 = MAYJ as example
ChDir "C:\My Documents\Paymaster\PRBackUp\"
ActiveWorkbook.SaveCopyAs PEDate & ECode & ".xls"
End Sub

All files contained in C:\My Documents\Paymaster\PRBackUp \are to be
e-mailed to
the person that preapres the Payroll every two weeks. When the
Payroll person opens
each file and Prints it out for their records << which is OK But
when they try and close
each one the Workbook_BeforeClose macro runs.

How can I remove this from the files that have been Saved as
052804MAYJ.xls so that
this situation doesn't occur? Any other advice you see let me know?
Tks in Advance,