Creating a List using a Form

[NFL]

It's my understanding that you can not expand a list using a drop-down menu
using data validation.


The list I like to show on the form is cell range E1:E90 (all in one column).

I looked at Microsofts help menu and tried to follow the pattern and its not
working.

Private Sub ListBox1_Click()

Sheets("Sheet1").Select
ListBox1.ColumnCount = 1
ListBox1.RowSource = "e81:e90"


ListBox1.ControlSource = "E5"
'Place the ListIndex into cell a6
ListBox1.BoundColumn = 0
End Sub

Thank you for your help!

 

[OssieMac]

See if the following examples help.

Private Sub ListBox1_AfterUpdate()
'Place the ListIndex into cell a6
Sheets("Sheet1").Range("A6") = ListBox1.ListIndex
End Sub

Private Sub ListBox1_Enter()
ListBox1.ColumnCount = 1
ListBox1.RowSource = "Sheet1!E81:E90"
ListBox1.ControlSource = "Sheet1!E5"
End Sub

 

[NFL]

That worked great except for one thing. I wanted the "selected" items to
appear in Cell E5, but all I get are numbers (0,1,2 ..etc.). . This is what
I entered on the command button. I don't know why I get numbers instead of
the items selected. Thank you again!!!

Private Sub CommandButton6_Click()
Sheets("Sheet1").Range("E5") = CSE_List.ListBox1.Value
CSE_List.Show
End Sub

Private Sub ListBox1_AfterUpdate()
'Place the ListIndex into cell E5
Sheets("Sheet1").Range("E5") = ListBox1.ListIndex
End Sub

Private Sub ListBox1_Enter()
ListBox1.ColumnCount = 1
ListBox1.RowSource = "Sheet1!E81:E90"
ListBox1.ControlSource = "Sheet1!E5"
End Sub

 

[NFL]

I got it figured out ... here's the code... Thank you again for your help!!!

Private Sub OKButton_Click()
Sheets("Sheet1").Range("E5") = Me.ListBox1.Value
CSE_List.Hide
End Sub

 

[OssieMac]

Some more info that might help you to understand what is occurring. Your
original post had a comment 'Place the ListIndex into cell a6. The ListIndex
is the number of the location of the selection starting from zero as the
first value. This is what I did.

The following part of the code dynamically places the selected value in E5
without additional code to reference the value. It is like a linked cell to
the selected value.
ListBox1.ControlSource = "Sheet1!E5"

 

[NFL]

Thank you for your response and exlanation. It helps a lot to see and
understand what is going on.