How to display Items in ListBox

[sam]

HI.

I have two ListBox(ListBox1&ListBox2) in Worksheet1. The amount of
items of ListBox2 depends on the choice of items in ListBox1.

On Worksheet2, I build a

Private Sub ListBox1_Click()
On Error Resume Next
With ListBox2
.ListFillRange = Range("DetailInfoAddress").Value
End With

End Sub


I mapped the cells relationship between ListBOX1 and ListBOX2 through
the property"LinkedCell". The DetailInfoAddress is the name for one
cell in worksheet2. It stands for a range info
like:Worksheet2!$B$15:$B$17.

However,this VBA program couldn't display the Items in the ListBox2.

Could you Fix this program for me?Thank you in advance!!!

Regards

Sam

 

[Kletcho]

I think your problem lies in that you are using a list box from the
forms toolbar and not a list box from the control toolbox. You have
much greater control over listboxes created through the control
toolbox.

Also where do you want the list items displayed? In a pop-up, certain
cells, the immediate window?

 

[sam]

HI,

I used listbox from the control toolbox. And I want the list of item
displayed in the certain cells of listbox2.

In my opinion, I think the property ".listFillRange" of ListBox2 can
load the info from the "DetailInfoAddress". However, it doesn't work.
Do you have some ideas?

Regards

Sam

 

[Kletcho]

Sorry I misunderstood what you were trying to accomplish.

It might be easier to loop through each cell in the named range one at
a time as opposed to assigning the whole range as the source?

For each cell in sheet2.Range("DetailInfoAddress")
With ListBox2
.additem cell.Value
End With
Next cell

 

[sam]

Hi.

I tried this code. But it doesn't work.

I pasted this code by clicking my listbox1.

Private Sub ListBox1_Click()
On Error Resume Next
For Each cell In Sheet2.Range("DetailInfoAddress")
With ListBox2
.AddItem cell.value
End With
Next cell
End Sub

In my case, "DetailInfoAddress" is the name of one cell in sheet2. Its
value depends
on the item choice of Listbox1. I used the linkedcell property to
connect this two listbox
Here is one example of "DetailInfoAddress" value, Sheet2!$B$2:$B$6.

Could you help me to try this code again? Thank you!

Best Regards

Sam

 

[Kletcho]

You were very close with your original code. The only difference is
you do not need the named range within the range property. Try these:

If the listbox is directly on the worksheet:
Sheet1.ListBox1.ListFillRange = "MyNamedRange"

If the listbox is in a userform:
Me.ListBox1.ListFillRange = "MyNamedRange"

Sorry for leading you off track originally.

 

[sam]

Hi,

I tried this code again. The result showing on the listbox2 is
something like Sheet2!$B$2:$B$6.
What i want to present in the listbox is the value from the the range.

because this range is variable. it depends on the choice in listbox1.
So i use a cell(named DetailInfoAddress") in sheet2 to represent the
range. based on differest choice on listbox1, the value in the cell
"DetailInfoAddress" can be "Sheet2!$B$2:$B$6",
"Sheet2!$B$7:$B$10","Sheet2!$B$11:$B$15",etc. Then I want show the
value in the specific range like "Sheet2!$B$2:$B$6"or
"Sheet2!$B$7:$B$10",etc.

Do you get my problem?

Thank you!!!

Best Regards

Sam

 

[Kletcho]

Ah I see. Let's try one more:

Sheet1.ListBox1.ListFillRange = Sheet1.Range("mine").Rows(1).Value

This only works if the first row in the named range has the value you
are looking for. If there are multiple values in the named range then
you would need to loop through it like my previous post showed.

Something else to consider is that you can use a dynamic named range
using the offset formula that might make things a little easier. Then
you could refer to the named range directly. Look up dynamic named
ranges on the web.