Display Listbox Selected entry

[WillBurge]

Hello all again,

I have a form control listbox, which is populated with filenames from a specific directory. When the user selects an entry I need that text to appear in a cell in the workbook. This is then concatenated with a folder path to open the file to copy and paste some data out into another workbook. I have tried...

 Sub GetWrbkbkname()
 
Dim strlist As String
strlist = Sheet1.Listbox18.Text
Sheet1.Cells(1, 1) = strlist
End Sub

But receive the method or data member not found error. The cell link property just returns the position in the list! I need the text!!!

The only workound I could think of was populating a space in the SS with the directory contents and using the cell link value in a choose function to return the selected workbook name.

Any help greatly appreciated!!

Thanks guys!

 

[WillBurge]

That looks like an ActiveX control from the "Toolbars", "Control ToolBox"
If it is this should work

Private Sub ListBox18_Change()
With ListBox1
    Range("B1") = .List(.ListIndex)
 End With
End Sub

This code fails on Range.... Error 424 - Object required

 

[WillBurge]

I have solved this, bit of a dirty workaround for anyone that is interested. Used the linked cell (that generates a value based on the row selected), part of the issue with using a form control of an activeX, used some vba to return a list of files in the target directory, and then used the =choose function to pick from that list, with the index number being the linked cell. Works, but dirty. Also will run into some issues when there is over 30 files thanks to the inherent function restrictions. Thanks MickG for your suggestions!

 

[MickG]

If you right click the list Box then look in the Names Box (top left of sheet) you will see its name. If there are spaces in the Name like :- "List Box 18" then its a forms control.
If it is a forms control , you should have the option, on right click to "Assign Macro" ???
What is the situation , is it working ????

 

[WillBurge]

If you right click the list Box then look in the Names Box (top left of sheet) you will see its name. If there are spaces in the Name like :- "List Box 18" then its a forms control.
If it is a forms control , you should have the option, on right click to "Assign Macro" ???
What is the situation , is it working ????

It has spaces, therefore - form control. I can right click and assign macro, but the option from there is only to edit an existing. The code you provided earlier runs fine but did not generate the selected value in the cell, 1,1.