Add this extra code

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "G3" '<== change to suit

On Error GoTo ws_exit
Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
Call GetWordDocument
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"MarkC" wrote in message
...
Thank you, 1 more question!! (sorry for this, but your help is really
appreciated) If I run the macro from the code view it works, but nothing
happens from the normal spreadsheet view when you insert a Y OR N in cell
G3?? Any suggestions, I tried to put a button on the spreadsheet to
create
the form linking it to the vba you have supplied me, but that just says
compile error.

Thank you again,
Mark

"Bob Phillips" wrote:

First, you need to put it in a sub.

Second, as they are forms to fill-in, you need to make Word visible, and
not
quit after opening the docs.

Try

Sub GetWordDocument()
Dim oWordApp As Object
Dim oDoc As Object
Dim sFilename As String

Set oWordApp = CreateObject("Word.Application")
If Range("G3").Value = "Y" Then
sFilename = "C:\Documents and Settings\mchallacombe\Desktop\" & _
"CRF_016\advice note\ST011AFO Issue 1 Advice
" &
_
"note AND Customs invoice.dot"
Else
sFilename = "C:\Documents and Settings\mchallacombe\Desktop\" & _
"CRF_016\advice note\ST011FO Issue 4 Manual "
&
_
"Advice Note.dot"
End If
Set oDoc = oWordApp.Documents.Open(sFilename)
oWordApp.Visible = True
Set oDoc = Nothing
Set oWordApp = Nothing
End Sub




--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

"MarkC" wrote in message
...
Hiya, I am getting a compile error 'INVALID OUTSIDE PROCEDURE',
I have changed the range to read G3 as this is is the cell that it will
read the y/n from (When we add a new entry it moves a row down (so row
3
is
always for 'new data'

Can you tell me whee I'm going wrong, thank you

Set oWordApp = CreateObject("Word.Application")
If Range("G3").Value = "Y" Then
Set oDoc = oWordApp.Documents.Open("C:\Documents and
Settings\mchallacombe\Desktop\CRF_016\advice note\ST011AFO Issue 1
Advice
note AND Customs invoice.dot")
Else
Set oDoc = oWordApp.Documents.Open("C:\Documents and
Settings\mchallacombe\Desktop\CRF_016\advice note\ST011FO Issue 4
Manual
Advice Note.dot")
End If
'do something
Set oDoc = Nothing
oWordApp.Quit
Set oWordApp = Nothing



"Bob Phillips" wrote:

Set oWordApp = CreateObject("Word.Application")
If Range("H1").Value = "Y" Then
Set oDoc = oWordApp.Documents.Open("C:\personal\bob\doc1.doc" )
Else
Set oDoc =
oWordApp.Documents.Open("C:\personal\jim\another.d oc")
End If
'do something
Set oDoc = Nothing
oWordApp.Quit
Set oWordApp = Nothing

If your VBA is non-existent, what will you do with the doc when it is
opened?

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

"MarkC" wrote in message
...
Hi, my VBA knowledge is non existent!! How do I do that ?

"Bob Phillips" wrote:

Just change the logic to do the test before the documents open in a
simple
If ... Else test.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in
my
addy)

"MarkC" wrote in message
...
How does this work if there is a N in the column, as that will
have
to
open
the other word doc...sorry should have explained this
more...Thanks
for
the
reply.



"Bob Phillips" wrote:

If Range("H1").Value = "Y" Then
Set oWordApp = CreateObject("Word.Application")
Set oDoc =
oWordApp.Documents.Open("C:\personal\bob\doc1.doc" )
'do something
Set oDoc = Nothing
oWordApp.Quit
Set oWordApp = Nothing
End If



--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail
in
my
addy)

"MarkC" wrote in message
...
Can anyone help please,I am looking for a macro that will open
a
word
template based upon a 'Y' or 'N' answer in an excel column
(column
G).

Can this be done?