using winxp and msoffice 2003

I am trying to open a file using the onclick event in my listbox.
But all I am capturing is the index number of the file and not the file
name that is actually the item in the list. So when the directory is
opened and searched for the file, it cannot locate the file because the
filename is not the same as the listbox index number.
Here is the routine I am using.

Private Sub lbVendor_Click()
Dim fname As String
fname = lbVendor.ListIndex
ChDir "C:\TestDir\Matt-House"
Workbooks.Open fname
End Sub

Could you tell me how to catch the actual filename as it is listed in
the listbox?

Thanks
Joanne

Joanne,

The ListIndex property returns the 0-based number of the item in the list.
To get the actual value, use the Value property of

With lblVendor
fname = .List(.ListIndex)
End With


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting
www.cpearson.com
(email on the web site)



"Joanne" wrote in message
...
using winxp and msoffice 2003

I am trying to open a file using the onclick event in my listbox.
But all I am capturing is the index number of the file and not the file
name that is actually the item in the list. So when the directory is
opened and searched for the file, it cannot locate the file because the
filename is not the same as the listbox index number.
Here is the routine I am using.

Private Sub lbVendor_Click()
Dim fname As String
fname = lbVendor.ListIndex
ChDir "C:\TestDir\Matt-House"
Workbooks.Open fname
End Sub

Could you tell me how to catch the actual filename as it is listed in
the listbox?

Thanks
Joanne

Thanks for the pointer Chip
Chip Pearson wrote:

Joanne,

The ListIndex property returns the 0-based number of the item in the list.
To get the actual value, use the Value property of

With lblVendor
fname = .List(.ListIndex)
End With

Chip
This works perfectly on my home lap top - but when I transport the code
to my work computer, change the path to reflect the path at work
("P:\House") I get an error saying that the file cannot be found. I have
tried it on several files with the same result. Doing step thru fname
does = the listindex value giving me the filename I requested. Just will
not go to the subdirectory and find the matching filename and open it
up.
Any ideas why it is working on one computer and not on the other?


Private Sub lbVendor_Click()
Dim fname As String
With lbVendor
fname = .List(.ListIndex) 'capture the value of the list index#
selected
End With
ChDir "C:\TestDir\Matt-House"
Workbooks.Open fname
End Sub

Thank you




Chip Pearson wrote:

Joanne,

The ListIndex property returns the 0-based number of the item in the list.
To get the actual value, use the Value property of

With lblVendor
fname = .List(.ListIndex)
End With

You either need to include the path in the list box entry, or have some way
of determining and setting the path.
You are currently changing the CurDir with
ChDir "C:\TestDir\Matt-House"

which is obviously wrong as you need:
P:\House

Here's one way to get the user to select the folder:
http://vbnet.mvps.org/code/browse/browsefolders.htm

Depending on your version of Excel, it may have a built-in BrowseForFolder
(something like that anyway) method.

NickHK

"Joanne" wrote in message
...
Chip
This works perfectly on my home lap top - but when I transport the code
to my work computer, change the path to reflect the path at work
("P:\House") I get an error saying that the file cannot be found. I have
tried it on several files with the same result. Doing step thru fname
does = the listindex value giving me the filename I requested. Just will
not go to the subdirectory and find the matching filename and open it
up.
Any ideas why it is working on one computer and not on the other?


Private Sub lbVendor_Click()
Dim fname As String
With lbVendor
fname = .List(.ListIndex) 'capture the value of the list index#
selected
End With
ChDir "C:\TestDir\Matt-House"
Workbooks.Open fname
End Sub

Thank you




Chip Pearson wrote:

Joanne,

The ListIndex property returns the 0-based number of the item in the
list.
To get the actual value, use the Value property of

With lblVendor
fname = .List(.ListIndex)
End With

You're right about the path.
Was setting path as if on my workstation instead of on the network.
Thanks for jogging my brain awake.


NickHK wrote:

You either need to include the path in the list box entry, or have some way
of determining and setting the path.
You are currently changing the CurDir with
ChDir "C:\TestDir\Matt-House"

which is obviously wrong as you need:
P:\House

Here's one way to get the user to select the folder:
http://vbnet.mvps.org/code/browse/browsefolders.htm

Depending on your version of Excel, it may have a built-in BrowseForFolder
(something like that anyway) method.

NickHK

"Joanne" wrote in message
...
Chip
This works perfectly on my home lap top - but when I transport the code
to my work computer, change the path to reflect the path at work
("P:\House") I get an error saying that the file cannot be found. I have
tried it on several files with the same result. Doing step thru fname
does = the listindex value giving me the filename I requested. Just will
not go to the subdirectory and find the matching filename and open it
up.
Any ideas why it is working on one computer and not on the other?


Private Sub lbVendor_Click()
Dim fname As String
With lbVendor
fname = .List(.ListIndex) 'capture the value of the list index#
selected
End With
ChDir "C:\TestDir\Matt-House"
Workbooks.Open fname
End Sub

Thank you




Chip Pearson wrote:

Joanne,

The ListIndex property returns the 0-based number of the item in the
list.
To get the actual value, use the Value property of

With lblVendor
fname = .List(.ListIndex)
End With