Hi all

I wish to write a routine that adds the following code to apprimately 300
files

"Private Sub Workbook_Open()
Dim YearsService As Single
YearsService = Now() - Range("Start_Date")
MsgBox Range("First_Name") & " " & Range("Surname") & " has a service
record of " & _
Int(YearsService / 365) & " years and " & Int((YearsService Mod 365) /
31) & " months"
End Sub"

At the moment there is no Open Event code in the files

I already have the routine to open the files one by one, make standard
changes, (heading etc.) and save the files. That part works fine.

However, now the client wants me to add an Open Event such as the above
code.
I had a look at John Walkenbach's book on adding code to a Module or adding
a button but can't find a way of inserting the above into ThisWorkBook.

Thanks,
Peter Bircher
Kwazulu-Natal, SA

I guess you mean insert it using code?

http://www.cpearson.com/excel/vbe.htm
discusses this.

--
Regards,
Tom Ogilvy



"Pete" wrote:

Hi all

I wish to write a routine that adds the following code to apprimately 300
files

"Private Sub Workbook_Open()
Dim YearsService As Single
YearsService = Now() - Range("Start_Date")
MsgBox Range("First_Name") & " " & Range("Surname") & " has a service
record of " & _
Int(YearsService / 365) & " years and " & Int((YearsService Mod 365) /
31) & " months"
End Sub"

At the moment there is no Open Event code in the files

I already have the routine to open the files one by one, make standard
changes, (heading etc.) and save the files. That part works fine.

However, now the client wants me to add an Open Event such as the above
code.
I had a look at John Walkenbach's book on adding code to a Module or adding
a button but can't find a way of inserting the above into ThisWorkBook.

Thanks,
Peter Bircher
Kwazulu-Natal, SA

Check out this link...

http://www.cpearson.com/excel/vbe.htm

--
HTH...

Jim Thomlinson


"Pete" wrote:

Hi all

I wish to write a routine that adds the following code to apprimately 300
files

"Private Sub Workbook_Open()
Dim YearsService As Single
YearsService = Now() - Range("Start_Date")
MsgBox Range("First_Name") & " " & Range("Surname") & " has a service
record of " & _
Int(YearsService / 365) & " years and " & Int((YearsService Mod 365) /
31) & " months"
End Sub"

At the moment there is no Open Event code in the files

I already have the routine to open the files one by one, make standard
changes, (heading etc.) and save the files. That part works fine.

However, now the client wants me to add an Open Event such as the above
code.
I had a look at John Walkenbach's book on adding code to a Module or adding
a button but can't find a way of inserting the above into ThisWorkBook.

Thanks,
Peter Bircher
Kwazulu-Natal, SA

Thanks, Tom.
I had a look and it looks perfect - I am pretty sure it will do just fine.
Peter

"Tom Ogilvy" wrote in message
...
I guess you mean insert it using code?

http://www.cpearson.com/excel/vbe.htm
discusses this.

--
Regards,
Tom Ogilvy



"Pete" wrote:

Hi all

I wish to write a routine that adds the following code to apprimately 300
files

"Private Sub Workbook_Open()
Dim YearsService As Single
YearsService = Now() - Range("Start_Date")
MsgBox Range("First_Name") & " " & Range("Surname") & " has a service
record of " & _
Int(YearsService / 365) & " years and " & Int((YearsService Mod 365)
/
31) & " months"
End Sub"

At the moment there is no Open Event code in the files

I already have the routine to open the files one by one, make standard
changes, (heading etc.) and save the files. That part works fine.

However, now the client wants me to add an Open Event such as the above
code.
I had a look at John Walkenbach's book on adding code to a Module or
adding
a button but can't find a way of inserting the above into ThisWorkBook.

Thanks,
Peter Bircher
Kwazulu-Natal, SA

Also, thanks to you, Jim for the timely response.
As mentioned in the reply to Tom, I had a look and it looks like it will do
just fine.
Peter

"Jim Thomlinson" wrote in message
...
Check out this link...

http://www.cpearson.com/excel/vbe.htm

--
HTH...

Jim Thomlinson


"Pete" wrote:

Hi all

I wish to write a routine that adds the following code to apprimately 300
files

"Private Sub Workbook_Open()
Dim YearsService As Single
YearsService = Now() - Range("Start_Date")
MsgBox Range("First_Name") & " " & Range("Surname") & " has a service
record of " & _
Int(YearsService / 365) & " years and " & Int((YearsService Mod 365)
/
31) & " months"
End Sub"

At the moment there is no Open Event code in the files

I already have the routine to open the files one by one, make standard
changes, (heading etc.) and save the files. That part works fine.

However, now the client wants me to add an Open Event such as the above
code.
I had a look at John Walkenbach's book on adding code to a Module or
adding
a button but can't find a way of inserting the above into ThisWorkBook.

Thanks,
Peter Bircher
Kwazulu-Natal, SA