logistics of application and procedures

J

JMay

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,
 
J

JMay

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...
 
F

Frank Kabel

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
 
T

Tom Ogilvy

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.
 
Top