Declaring a variable?
 

I have this code which when a cell is selected loads up a certain wor
file. All the word files are stored in the same folder. Currently, whe
a cell is selected an option is shown which asks the user if they wan
to view the word file for that cell. The problem I have is that whe
Yes is selected, the code doesn't load up the word file I want, i
wants to load up the document named 'Target'. Do I have to declare thi
'Target' a variable. Very confused, any help would be great. The code
have is below:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim intResponse As Integer
Dim WrdApp As Object
Set WrdApp = CreateObject("Word.Application")

' Determines whether the cell has a BOM# within it
If Left(Target, 3) = "BOM" Then

' Displays a message box allow with options
intResponse = MsgBox("Would you like to open " & Target & " ?"
vbYesNo)

' Selects the outcome if Yes is chosen
If intResponse = vbYes Then

' Opens word file
With WrdApp
.Documents.Open Filename:="S:\Technical\Target"
ReadOnly:=True
End With

WrdApp.Visible = True

End If



End I

--
Message posted from http://www.ExcelForum.com

Declaring a variable?
 

"S:\Technical\Target",
is a string and every character is read *as*
a character, so Excel will have no way of
recognizing "Target" as the variable Target.
instead use:

Documents.Open Filename:="S:\Technical\" & Target.Value


--
Best Regards
Leo Heuser

Followup to newsgroup only please.

"pgoodale" skrev i en meddelelse
...
I have this code which when a cell is selected loads up a certain word
file. All the word files are stored in the same folder. Currently, when
a cell is selected an option is shown which asks the user if they want
to view the word file for that cell. The problem I have is that when
Yes is selected, the code doesn't load up the word file I want, it
wants to load up the document named 'Target'. Do I have to declare this
'Target' a variable. Very confused, any help would be great. The code I
have is below:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim intResponse As Integer
Dim WrdApp As Object
Set WrdApp = CreateObject("Word.Application")

' Determines whether the cell has a BOM# within it
If Left(Target, 3) = "BOM" Then

' Displays a message box allow with options
intResponse = MsgBox("Would you like to open " & Target & " ?",
vbYesNo)

' Selects the outcome if Yes is chosen
If intResponse = vbYes Then

' Opens word file
With WrdApp
Documents.Open Filename:="S:\Technical\Target",
ReadOnly:=True
End With

WrdApp.Visible = True

End If



End If


---
Message posted from http://www.ExcelForum.com/

Declaring a variable?
 

I think that's because that's precisely what these lines:

With WrdApp
.Documents.Open Filename:="S:\Technical\Target",
ReadOnly:=True
End With

tell it to do :-)

Looks like you've forgotten to concatenate something onto the end of
the filename string, presumably a value derived from the cell you've
just clicked on. Plus I'd expect that filename to end in .doc as it's
a word file.

HTH,
John