Word/Excel Macro on Open

[sjp0362]

We are updating our Word and Excel company templates (Office 2007) and have two different office locations. Currently, we have two separate templates for each master form (one for each office location). Does anyone know of a way that I could have one template in which upon selecting the template, a macro (or something else) would run that would ask which office location was desired so when the new document opened, the correct office address would automatically pop into my document?

 

[Loomah]

hi
for the workbook use the open event of ThisWorkbook - put your code in here:-

Private Sub Workbook_Open()
End Sub

Again, for word, put the code in the open event of the document. ie

Private Sub Document_Open()
End Sub

i think it's the 'new' event if you are using a template file (.dot/.dotx)

i never did much programming in office 07 but don't recall anything like this having changed

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​

 

[sjp0362]

Yeah, at least I know it is possible. Now I just have to figure out how to write the code or macro--any ideas on how to do this?

 

[Loomah]

hi
i think you have quite a project on your hands!

i don't know enough about word to help you there and i'm not usually around these forums as much as i would once have been.

i suggest posting in the vba forum (forum707) but with a full description of what you are hoping to achieve together with any code you may have already managed.

as an outline you could
1) have a user form that opens when the template is opened
2) code all the address information and popolate specific fields/cells depending on the choice.

the key is making the choice of location. would be neater if your users didn't have to select this themselves but that opens a whole field of programming i have no experience of!

good luck with it!

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​

 

[kjv1611]

For what you're trying to do, you could create a userform for that document/workbook. Then in the _Open() code, you open the form.

Here's a short and sweet example from

http://www.mrexcel.com/td0002.html

Private Sub Workbook_Open()
    frmOfficeAddress.Show [green]'frmOfficeAddress would be your new user form.[/green]
End Sub

 

[kjv1611]

Oh, and going forward, if your questions relate to VBA rather than non-code questions in Word/Excel, then you'd best ask those specific questions in the following forum:
forum707
(the forum Loomah was referencing)

 

[sjp0362]

Thank you to both of you. I was thinking that I needed to move over to the VBA forum now to complete my searching and implementing. I'll be totally new to this, but am willing to keep learning--just wish I had more hours in the day.