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!

 

[MickG]

Try something like this:-

Sub ListBox2_Change()
With ActiveSheet.Shapes("List Box 2").ControlFormat
    Sheets("Sheet1").Cells(1, 1) = .List(.ListIndex)
End With
End Sub

 

[WillBurge]

Many thanks for your swift reply Mick, I will give this a go now! Thanks again

 

[WillBurge]

Mick - as i think i am using listbox18, I changed all the 2's in your code to 18. Running the code, or clicking in the list box does not return any errors, nor does it populate 1,1. Any ideas why this might be happening?

 

[MickG]

If you right click the listbox you should see the Name in the "Names" Box at to left of screen.
If you Right click the Listbox and Select "Assign Macro", then click "New" you will Automatically get the code outline ready to enter the code, also---
If your List box is Listbox18 and a "forms" list box, it must be shown as below with the spaces:-

With ActiveSheet.Shapes("List Box 18").ControlFormat

Is this what you have. ???

 

[WillBurge]

Confirmed for Listbox 18, and yes, that is exactly how it appears in the editor.

 

[MickG]

I modified that last post, re mod :- have you created the code by the "Assign Macro" selection ???
And is it still not working ????

 

[WillBurge]

I dont have the option to create code when right clicking and pressing assign macro. I can only edit the existing code, which reads..

Sub ListBox18_Change()
With ActiveSheet.Shapes("List Box 18").ControlFormat
Sheets("Sheet1").Cells(1, 1) = .List(.ListIndex)
End With
End Sub

 

[MickG]

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

 

[WillBurge]

I am quite certain its a form control. It was an activeX control, but the file had to be 03 compatible and some of the code wasnt liked. So I changed it to a form control.