return filename

[Joanne]

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

 

[Chip Pearson]

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]

Thanks for the pointer Chip

 

[Joanne]

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

 

[NickHK]

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]

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.