Inserting Open Event code via VBA

[Pete]

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

[Tom Ogilvy]

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

[Jim Thomlinson]

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

[Pete]

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

"Tom Ogilvy" <[email protected]> wrote in message
news:[email protected]...
>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
>>
>>
>>
>>

[Pete]

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" <[email protected]> wrote in message
news:[email protected]...
> 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
>>
>>
>>
>>