Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
ListBox List Code?
I have combobox1, combobox2 and listbox1.
i am runnig the code from sheet1 in my below code only for the first item column 2 and three are displyed.. also how can I use dynamic range? is it possible to get unique item from sheet2 Range(Range ("A2"),Range("A65536").end(xlup)) as combobox1 list? Private Sub ComboBox1_Change() Dim sh As Worksheet Dim cell As Range Me.ListBox1.Clear Set sh = Sheets("sheet2") For Each cell In sh.Range("a2:a10") If cell.Value = Me.ComboBox1.Value And cell.Offset(0, 2).Value = ComboBox2.Value Then 'If cell.Offset(0, 2).Value = ComboBox2.Value Then Me.ListBox1.AddItem cell.Value Me.ListBox1.List(0, 1) = cell.Offset(0, 1).Value Me.ListBox1.List(0, 2) = cell.Offset(0, 2).Value 'End If End If Next cell End Sub Private Sub ComboBox2_Change() Dim sh As Worksheet Dim cell As Range Me.ListBox1.Clear Set sh = Sheets("sheet2") For Each cell In sh.Range("a2:a10") If cell.Value = Me.ComboBox1.Value And cell.Offset(0, 2).Value = ComboBox2.Value Then 'If cell.Offset(0, 2).Value = ComboBox2.Value Then Me.ListBox1.AddItem cell.Value Me.ListBox1.List(0, 1) = cell.Offset(0, 1).Value Me.ListBox1.List(0, 2) = cell.Offset(0, 2).Value 'End If End If Next cell End Sub Thanks for your help.. Abdul |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
ListBox List Code?
You're updating the 2nd and third columns of the first entry with these lines:
Me.ListBox1.List(0, 1) = cell.Offset(0, 1).Value Me.ListBox1.List(0, 2) = cell.Offset(0, 2).Value Me.ListBox1.List(Me.ListBox1.ListCount - 1, 1) = cell.Offset(0, 1).Value Me.ListBox1.List(Me.ListBox1.ListCount - 1, 2) = cell.Offset(0, 2).Value To save typing: With Me.ListBox1 .AddItem cell.Value .List(.ListCount - 1, 1) = cell.Offset(0, 1).Value .List(.ListCount - 1, 2) = cell.Offset(0, 2).Value End With And John Walkenbach shows a technique for getting a unique list into a listbox--you can use the same kind of thing for your combobox. http://j-walk.com/ss/excel/tips/tip47.htm Abdul wrote: I have combobox1, combobox2 and listbox1. i am runnig the code from sheet1 in my below code only for the first item column 2 and three are displyed.. also how can I use dynamic range? is it possible to get unique item from sheet2 Range(Range ("A2"),Range("A65536").end(xlup)) as combobox1 list? Private Sub ComboBox1_Change() Dim sh As Worksheet Dim cell As Range Me.ListBox1.Clear Set sh = Sheets("sheet2") For Each cell In sh.Range("a2:a10") If cell.Value = Me.ComboBox1.Value And cell.Offset(0, 2).Value = ComboBox2.Value Then 'If cell.Offset(0, 2).Value = ComboBox2.Value Then Me.ListBox1.AddItem cell.Value Me.ListBox1.List(0, 1) = cell.Offset(0, 1).Value Me.ListBox1.List(0, 2) = cell.Offset(0, 2).Value 'End If End If Next cell End Sub Private Sub ComboBox2_Change() Dim sh As Worksheet Dim cell As Range Me.ListBox1.Clear Set sh = Sheets("sheet2") For Each cell In sh.Range("a2:a10") If cell.Value = Me.ComboBox1.Value And cell.Offset(0, 2).Value = ComboBox2.Value Then 'If cell.Offset(0, 2).Value = ComboBox2.Value Then Me.ListBox1.AddItem cell.Value Me.ListBox1.List(0, 1) = cell.Offset(0, 1).Value Me.ListBox1.List(0, 2) = cell.Offset(0, 2).Value 'End If End If Next cell End Sub Thanks for your help.. Abdul -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
copying the contents of a drop down list from the listbox itself? | Excel Discussion (Misc queries) | |||
Drop Down/List w/Code and Definition, only code entered when selec | Excel Worksheet Functions | |||
DONT WANT TO SEE LISTBOX SELEXTED ITEMS WHEN COME BACK TO LIST BOX | Excel Discussion (Misc queries) | |||
Create ListBox (?) same as Find all Result List | Excel Programming | |||
Excel; vba; listbox how to reorganize list in alfabetical order | Excel Programming |