How to open a different word document when selection in excel drop down box changes (VBA)

[hlg0035]

Hello.

I am very new to VBA and am trying to develop a code that opens up a different word document when the selection in the excel drop down box changes. I know one option is to do an if/then statement, but I am unsure of the syntax.

Any help would be appreciated!

Here is my code for opening up a template in Word:

Set WrdApp = CreateObject("Word.Application")
WrdApp.Visible = True
Set WrdDoc = WrdApp.Documents.Open("C:\Users\" & UserID & "\Desktop\Quantien Evaluation with Certus and WiBox Template.docx")

[Solus Rankin]

Is the "drop down" box a datavalidation list? If so what cell is it in?

You want it opened after every change? Or only if changed to a certain value?

[hlg0035]

Yes, it's a datavalidation list. It's in cell A2. I want a different word document to open if a different selection from the dropdown box is chosen.

For example, there are 5 selections to make in the drop down box. For each selection, I would like my program to open a different word document template.

Basically, the entire program is this: Open up word template, change variables with find/replace, save word document, save as pdf, open up outlook new email and attach pdf.

[Solus Rankin]

Will it be a form letter for Outlook? If that is the case couldn't the template be a sheet in your workbook?

Is it necessary to use Word?

[hlg0035]

Yes it is necessary to use word, the templates have already been created. They are template contracts (there are about 15 templates I am trying to do this for- which is why I am wanting to try the drop down box in excel - less bulky). Instead of creating the contracts manually using a template, I am trying to create a program that automates them with a few bits of information into some excel cells (like customer name, city, state, etc.), then opens up a new email with this automated contract attached.

The template could be a sheet in the workbook, but other people would be using this tool and I wouldn't want to cause any confusion. I would rather everything be in the macro and all they would have to do was enter in some information click a button and everything is done.

[Solus Rankin]

This is pretty simple and doesn't include any error trapping.

Might need an example to work with if it gets any more involved.

Please Login or Register  to view this content.

[hlg0035]

Solus,

I have tried this code and it worked in a test run but when I incorproated it into my program I get a 424 run time error.

Dim UserId As String

UserId = Sheets("QuantienEvaluations").Range("M5").Value2


Set WrdApp = CreateObject("Word.Application")
WrdApp.Visible = True

If Sheets("QuantienEvaluations").Range("A2") = "QuantienAerisOnly" Then Set WrdDoc = WrdApp.Documents.Open("C:\Users\" & UserId & "\Desktop\Quantien With Aries Only.docx")
If Sheets("QuantienEvaluations").Range("A2") = "QuantienAerisCertus" Then Set WrdDoc = WrdApp.Documents.Open("C:\Users\" & UserId & "\Desktop\Quantien With Aeris and Certus.docx")
If Sheets("QuantienEvaluations").Range("A2") = "QuantienAdditionalWiBoxAerisOnly" Then Set WrdDoc = WrdApp.Documents.Open("C:\Users\" & UserId & "\Desktop\Quantien With Aeris and Additional Wibox.docx")
If Sheets("QuantienEvaluations").Range("A2") = "QuantienAdditionalWiBoxAerisCertus" Then Set WrdDoc = WrdApp.Documents.Open("C:\Users\" & UserId & "\Desktop\Quantien With Aeris and Certus and Additional WiBox.docx")



WrdDoc.SaveAs2 Filename:= _
"C:\Users\" & UserId & "\Desktop\ " & CustomerName & " " & City & " " & State & " " & CustomerNumber & " " & "Quantien Evaluation Agreement" & " " & Month(Now()) & "." & Day(Now()) & "." & Year(Now()) & ".docx" _
, FileFormat:=13, LockComments:=False, _
Password:="", AddToRecentFiles:=True, WritePassword:="", _
ReadOnlyRecommended:=False, EmbedTrueTypeFonts:=False, _
SaveNativePictureFormat:=False, SaveFormsData:=False, SaveAsAOCELetter:= _
False, CompatibilityMode:=14

[Solus Rankin]

On what line does it error?

Realistically to trouble shoot this you will probably have to attach your workbookas an example.