Listbox item on click updates the same listbox

[Archiello]

Hello!

I have a huge problem. I am new to VB, and there is an issue that I cant solve.

Am am constructing a small search "module". A Userform based, where I have
1. 1 Listbox
2. 5 Texboxes

Listbox: for clicking, acts like option/alternatives
Textboxes for displaying the levels, like brand, model, temperature etc

By initialising the Userform a Range from sheet1 is beeing displayed in the Listbox1.
My problem is, if I click on an Item in the Listbox1 I would like to have the value what was clicked to appear in the textbox1, and in that moment the Listbox1 should be updated according to what is beeing displayed in textbox1. Sorry for my bad english

example: Listbox1--> brandname, onlclick the subattributums appear in the same listbox. Then, if I click the subattributum, the next textbox will be filled and goes further. There is another way to solve the issue, but would like to have it userform (cpearson:LinkedListBoxes). Any Ideas could save me



Thank you

[Archiello]

HI!

I am going to make the question simple:

1 Listbox , 5 Textbox

You click an Item in the Listbox, the event appears in Textbox1.
If Textbox1 is "A" the Listbox should list the components assigned to "A", so the Listbox1 should show the new data.

If you click on the listbox1 updateted item, the second textbox should show what you clicked, lets say "B".

Listbox1 should be refreshed to those items that belongs to "B"

then you click again again again and again...

PLEEEEEEEEEEEEEEEEEEEEEEEEEEEASE Help!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

[mikerickson]

Can you give us a small sample of data and the results you want.

[Archiello]

Hi! Thanks for replying.

Well, I had to do it on a novice level with a lot of macros: very slow.
The database is quite big: 60000 rows full with data. Thats why I made named ranges for the first textbox.
that you understand. there is a brand "A", like suzuki, but suzuki has a lot of models.
so on a worksheet I made in 1 columne all the models that belongs to brand "A", in this case suzuki,
then in the next columne (same worksheet) all the models that belongs to brand "B", and so on.
Thats how excel knows the rowsource for the first textbox

I got the motor work, but its quite slow.
its far not finished, but does the work, on the end a code for the listbox1

Once again: listbox1 is just for picking, and on doubleclick an item in listbox1 the next level bind to the clicked one should appear,
than the next and the next. in the end, you have another listbox2,
where all the material defined and selected by the user appears. but thats another theme

question was

first selection that appears in listbox:

Metal
Wood
plastik

(comes from rowsource)

if you dbcklick "metal",
all the subattributum appear in the same listbox1 for "metal" but textbox1 is filled with clicked choice (metal), then:

shiny
matt

appears in listbox1
if you click matt, textbox2 is filled with matt, and in listbox1 all the next subattributums that are bind to matt appears:

red
white
black


etc
etc


[VBA]Private Sub UserForm_Initialize()
ListBox1.RowSource = "Marke"
End Sub



Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
With UserForm1

If TextBox2.Text = "" Then

Sheets("keplet").Range("i1") = ListBox1.List(ListBox1.ListIndex)
ListBox1.RowSource = TextBox2.Value

ElseIf TextBox3.Text = "" Then
Sheets("keplet").Range("j1") = ListBox1.List(ListBox1.ListIndex)
Application.ScreenUpdating = False
Call ShowDialog 'this call a userform+macro, because it take to much time to calculate (progressbar)
Application.ScreenUpdating = True
Dim MyData As Range
Dim r As Long
With Me.ListBox1
.RowSource = ""
Set MyData = Worksheets("Suche").Range("A2:A100")
.List = MyData.Cells.Value
For r = .ListCount - 1 To 0 Step -1
If .List(r, 0) = "" Then
.RemoveItem r
End If
Next r
End With

ElseIf TextBox4.Text = "" Then
Sheets("Suche").Range("B1") = ListBox1.List(ListBox1.ListIndex)
TextBox4.Text = Sheets("Suche").Range("B1")
Application.ScreenUpdating = False
Application.Run "'xy.xls'!punoaso"
Application.ScreenUpdating = True
Dim MyData1 As Range
Dim rr As Long
With Me.ListBox1
.RowSource = ""
Set MyData1 = Worksheets("Suche2").Range("a2:a100")
.List = MyData1.Cells.Value
For rr = .ListCount - 1 To 0 Step -1
If .List(rr, 0) = "" Then
.RemoveItem rr
End If
Next rr
End With

ElseIf TextBox5.Text = "" Then
Sheets("keplet").Range("n1") = ListBox1.List(ListBox1.ListIndex)
TextBox5.Text = Sheets("keplet").Range("n1")
Application.ScreenUpdating = False
Application.Run "'xy.xls'!pundi"
Application.ScreenUpdating = True
Dim MyData2 As Range
Dim rrr As Long
With Me.ListBox1
.RowSource = ""
Set MyData2 = Worksheets("Suche3").Range("a2:a100")
.List = MyData2.Cells.Value
For rrr = .ListCount - 1 To 0 Step -1
If .List(rrr, 0) = "" Then
.RemoveItem rrr
End If
Next rrr
End With

ElseIf TextBox6.Text = "" Then
Sheets("keplet").Range("p1") = ListBox1.List(ListBox1.ListIndex)
TextBox6.Text = Sheets("keplet").Range("p1")
Application.ScreenUpdating = False
Application.Run "'xy.xls'!punito"
Application.ScreenUpdating = True
Dim MyData3 As Range
Dim rrrr As Long
With Me.ListBox2
.RowSource = ""
Set MyData3 = Worksheets("Suche4").Range("d2:d100")
.List = MyData3.Cells.Value
For rrrr = .ListCount - 1 To 0 Step -1
If .List(rrrr, 0) = "" Then
.RemoveItem rrrr
End If
Next rrrr
End With
End If
End With
End Sub

Sub ShowDialog()
UserForm2.LabelProgress.Width = 0
UserForm2.Show
End Sub[/VBA]

[Archiello]

What I would like: that is should be fast and programetically, not with recorded macros, I know its possible, but I dont know to much of programming

[shrivallabha]

Maybe you should have a look at Treeview option. It may prove little difficult to begin with but may prove to be a better option.

Ken puls' website has a very good example in this regard:
http://www.excelguru.ca/node/85

[Archiello]

Hi!

Thank you. I have no experience with trees from excel. You know, this thread is more: updating the a listbox on doubleclick regarding the reference Textbox A wich has a datalist bind to it. do you understand? If you click the updated listbox, the second textbox is filled, and the data bind to the second text appears in the same listbox. Talking about 1 listbox... meny textboxes. like a tree...

Why I dont want any trees is because its quite uncustomer-friendly. Well in my case, in our case,... if we talk about trees.

I didnt try it yet, so sorry for telling you my opinion about something I havent tried. But the tree in fact is fantastic option

search:
1. it should be fast
2. if you click a listed item in listbox1, the clicked item (text) appears on another place of the userform, lets say textbox1, then the updating is done, regaring the text in textbox1.

its lika a category: (and yes, you are right: like a tree), but I hate to close and open... and so do the customers.

Your idea will be tested, I will do so, and I am sure it will fit another important theme in the searchmodule, so THANKS for saving me the time to set it here

till then, if you have something that match the upper sentences programmetically, instead of macros, that calculates with formulas a long-long time, please help me... I have still 1-2 weeks to end this case...

KR

Archiello

[Archiello]

I am going to tell you a small sample, why I dont want trees:

Lets say we have an textile industry

1. we have jeans, pullovers t-shirts etc (200 sort)
2. then if you select lets say, gloves
3. winter, summer, daily fashion, etc apperas. and then on winter
4. s,m, l , xl, xll etc..
5. red, white, pink yellow....
6. etc etc

ofcourse not in this resolution, but if you check: how many trees and subtrees do you have to open, and then get back, where you you first were, or just a step back? do you see? trees not possible.... sorry

[shrivallabha]

Replying little late. It was just a suggestion, nothing more. The choice always remains with you. I tried my hand at your way of doing things. I have built up small example (which has similar logic as that of treeview) but it is in your format i.e. Listbox and 5 textboxes.

Please see the attachment. I have prepared code in 2003 as I was not sure about the version you are using. Should work in 2007+ as well.

Acknowledgment: John Walkenbach code here:
http://j-walk.com/ss/excel/tips/tip47.htm

Check the attached Excel File.

The VBA Code:
[VBA]Dim lLastRow As Long
Dim i As Integer
Dim ncUnique As New Collection
Private Sub List1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
'********************************************************
'The Exit Sub ensures only one If...End If statement gets
'executed!
'********************************************************
If Sel1.Value = "" Then
Sel1.Value = List1.Value
List1.Clear
Set ncUnique = Nothing
For i = 1 To lLastRow
On Error Resume Next
If Range("A" & i).Value2 = Sel1.Value Then
ncUnique.Add Range("B" & i).Value2, CStr(Range("B" & i).Value2)
End If
On Error GoTo 0
Next i
For Each Item In ncUnique
List1.AddItem Item
Next Item
Exit Sub
End If
If Sel2.Value = "" Then
Sel2.Value = List1.Value
List1.Clear
Set ncUnique = Nothing
For i = 1 To lLastRow
On Error Resume Next
If Range("A" & i).Value2 = Sel1.Value And Range("B" & i).Value2 = Sel2.Value Then
ncUnique.Add Range("C" & i).Value2, CStr(Range("C" & i).Value2)
End If
On Error GoTo 0
Next i
For Each Item In ncUnique
List1.AddItem Item
Next Item
Exit Sub
End If
If Sel3.Value = "" Then
Sel3.Value = List1.Value
List1.Clear
Set ncUnique = Nothing
For i = 1 To lLastRow
On Error Resume Next
If Range("A" & i).Value2 = Sel1.Value And Range("B" & i).Value2 = Sel2.Value And _
Range("C" & i).Value2 = Sel3.Value Then
ncUnique.Add Range("D" & i).Value2, CStr(Range("D" & i).Value2)
End If
On Error GoTo 0
Next i
For Each Item In ncUnique
List1.AddItem Item
Next Item
Exit Sub
End If
If Sel4.Value = "" Then
Sel4.Value = List1.Value
List1.Clear
Set ncUnique = Nothing
For i = 1 To lLastRow
On Error Resume Next
If Range("A" & i).Value2 = Sel1.Value And Range("B" & i).Value2 = Sel2.Value And _
Range("C" & i).Value2 = Sel3.Value And Range("D" & i).Value2 = Sel4.Value Then
ncUnique.Add Range("E" & i).Value2, CStr(Range("E" & i).Value2)
End If
On Error GoTo 0
Next i
For Each Item In ncUnique
List1.AddItem Item
Next Item
Exit Sub
End If
If Sel5.Value = "" Then
Sel5.Value = List1.Value
Exit Sub
End If
'********************************************************
' Reset if the person wants to try new combination
'********************************************************
If Sel5.Value <> "" Then
Sel1.Value = ""
Sel2.Value = ""
Sel3.Value = ""
Sel4.Value = ""
Sel5.Value = ""
List1.Clear
MsgBox "Reset! Please make new choices!!"
Call UserForm_Initialize
End If
End Sub
Private Sub UserForm_Initialize()
lLastRow = Range("A" & Rows.Count).End(xlUp).Row
'********************************************************
' Emptying the list and collection
'********************************************************
Set ncUnique = Nothing
List1.Clear
'********************************************************
' Thanks to := http://j-walk.com/ss/excel/tips/tip47.htm
'********************************************************
For i = 1 To lLastRow
'********************************************************
' This suppresses the error due to duplicate value (key)
'********************************************************
On Error Resume Next
ncUnique.Add Range("A" & i).Value2, CStr(Range("A" & i).Value2)
'********************************************************
' Resetting the error suppression so that any other error
' does not get suppressed
'********************************************************
On Error GoTo 0
Next i
'********************************************************
' Looping through all items in the collection
'********************************************************
For Each Item In ncUnique
List1.AddItem Item
Next Item
End Sub
[/VBA]

Hth,

[xemdarx]

Hi, can you add back button to listbox?