Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to display Items in ListBox
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to display Items in ListBox
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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to display Items in ListBox
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to display Items in ListBox
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to display Items in ListBox
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to display Items in ListBox
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. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to display Items in ListBox
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to display Items in ListBox
Ah I see. Let's try one mo
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Always display items AND Include hidden items in totals | Excel Discussion (Misc queries) | |||
Adding Items to a ListBox-Unique Items Only | Excel Programming | |||
Items in a Listbox | Excel Programming | |||
Fill a ListBox with items using VBA | Excel Programming | |||
Sort Listbox Items | Excel Programming |