Solved: List Box Selection To Text Box

[sooty8]

Hi All

I have a UserForm with a ListBox which is populated via RowSource, on clicking on a Customer Name 6 Text Boxes are populated and added to the sheet OK no problem. I have added a ComboBox and it is populated by the "Add Item" method. How would I alter the code below so that if I select Yes in the ComboBox it will fill the Textboxes as below. However if I select No in the ComboBox it will fill the TextBoxes as shown here.

UserForm1.Tb6.Value = Lb1.List(, 7) <<<<< On Choosing No
UserForm1.Tb7.Value = Lb1.List(, 8) <<<<< On Choosing No


On Choosing Yes

Private Sub Lb1_Click()
Sheets("Customers").Select
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("Customers")
rownum = Lb1.ListIndex
UserForm1.Tb3.Value = Lb1.List(, 3)
UserForm1.TB1.Value = Lb1.List(, 1)
UserForm1.Tb2.Value = Lb1.List(, 2)
UserForm1.Tb4.Value = Lb1.List(, 4)
UserForm1.Tb6.Value = Lb1.List(, 5)
UserForm1.Tb7.Value = Lb1.List(, 6)
'''This works OK'''
End Sub


Any help much appreciated

Cheers

Sooty8.

[Bob Phillips]

[vba]

Private Sub Lb1_Click()
Sheets("Customers").Select
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("Customers")
rownum = Lb1.ListIndex
With UserForm1
If ComboBox1.Value = "Yes" Then
.Tb3.Value = Lb1.List(, 3)
.TB1.Value = Lb1.List(, 1)
.Tb2.Value = Lb1.List(, 2)
.Tb4.Value = Lb1.List(, 4)
.Tb6.Value = Lb1.List(, 5)
.Tb7.Value = Lb1.List(, 6)
Else
.Tb6.Value = Lb1.List(, 7)
.Tb7.Value = Lb1.List(, 8)
End If
End With
End Sub
[/vba]

but wouldn't a checkbos work better, or radio buttons?

[sooty8]

Hi El Xid

Your code works OK and thank you -- I was actually trying to be clever and shorten what I really needed the Columns go upto AA so its really like a selection process on what the customer has purchased -- I just used Yes and No in the combobox hoping it would be something simple and I could expand on any help and code that was made available to me.
The Combobox would have items like cutlery, cups, saucers etc it seems I would be coding for ever. Any suggestions gratefully accepted and again thank you for your help.

Cheers

Sooty8.

[Bob Phillips]

This is untetsed, but perhaps you could work with this

[vba]

Private Sub Lb1_Click()
Dim aryItems As Variant

Sheets("Customers").Select
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("Customers")
rownum = Lb1.ListIndex
With UserForm1
Select Case ComboBox1.Value
Case "cutlery": aryItems = Array(1, 2, 3, 4, 5, 6)
Case "cups": aryItems = Array(7, 8)
Case "saucers": aryItems = Array(7, 8)
End Select
For i = LBound(aryItems) To UBound(aryItems)
.Controls("Tb" & aryItems(i)).Value = .Lbl1.List(, aryItems(i))
Next i
End With
End Sub
[/vba]

[sooty8]

Hi Xld

I'm losing!!! it with this problem - laptop is dangling by its hinges outside the window of our office -- can get it working OK with the 1st selection in the combobox making another selection kicks in debugger with Run-Time Error Could not find the specified object
Private Sub Lb1_Click()
Dim aryItems As Variant
Sheets("Customers").Select
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("Customers")
rownum = Lb1.ListIndex

With UserForm1
Select Case Cb1.Value
Case "Cutlery": aryItems = Array(1, 2, 3, 4, 5, 6)
Case "Cups": aryItems = Array(7, 8) <<< have tried this way
Case "Saucers": aryItems = Array(1, 2, 3, 4, 9, 10) <<< have tried this way all to no avail >>>
End Select
For i = LBound(aryItems) To UBound(aryItems)
.Controls("Tb" & aryItems(i)).Value = .Lb1.List(, aryItems(i)) << debug kicks in here>>
Next i
End With
End Sub
any further idea's on how to solve this would be most appreciated

Cheers

Sooty8

[Bob Phillips]

It works fine for me with the data I have setup (I have no idae what is in Lb1, so I invented it).

[rory]

What is the value of aryItems(i) at the point the code errors and do you have a control by that name?

[sooty8]

Hi

.Controls("Tb" & aryItems 'displays 7 which is correct'(i)).Value = .Lb1.List ' displays 224 which is correct (, aryItems ' displays 7 which I assume should be 8 (i)) <<<< as the array is (7, 8)

For the Listbox -- I have Bound Column 1 -- Column Count 12 -- RowSource Customers!A2:K150 at the moment just trying to get it all to run

Cheers

Sooty8

[rory]

As that first bit made no sense to me, any chance you can post a sample?

[sooty8]

Hi

The arrays are found on the customers sheet and are displayed in TextBoxes 5 & 6 and then added to the customers invoice with all their purchases and data retrieved from the customers sheet.. Or that was the original idea. Hair tearing out time.

Cheers

Sooty 8

Private Sub Lb1_Click()
Dim aryItems As Variant
Sheets("Customers").Select
Dim i As Long
Dim ws As Worksheet
Set ws = Worksheets("Customers")
rownum = Lb1.ListIndex

With UserForm1
Select Case Cb1.Value
Case "Cutlery": aryItems = Array(1, 2, 3, 4, 5, 6)
Case "Cups": aryItems = Array(7, 8)
Case "Saucers": aryItems = Array(9, 10)
End Select
For i = LBound(aryItems) To UBound(aryItems)
.Controls("Tb" & aryItems(i)).Value = .Lb1.List(, aryItems(i))
Next i
End With
End Sub

[rory]

And you definitely have textboxes called Tb9 and Tb10?

[rbrhodes]

Hi All,

Playing with this...does this help or did I misunderstand?

[sooty8]

Hi

Having a chat with a friend on Friday evening about my problem with List Boxes & Text Boxes he told me of a solution to a problem he had and suggested using two ComboBoxes and a ListBox he sent me some code over the weekend and advised me to change the names of all the boxes etc to suit my needs - to be honest it took me hours to get it working however I submit the code below after mega alterations and await your comments -- it does work - other members may find it helpful and everybody who tried to help many thanks -- Sooty8

Private Sub Cb2_Click()
startcoldist = 6
colqtydist = 2
colnumdist = (Cb2.ListIndex * colqtydist) + startcoldist
startrowdist = 2
totalrowdist = 2
If Trim(Me.Cb1.Value) <> "Select Item" Then
totalrowdist = Sheets(Trim(Me.Cb1.Value)).UsedRange.Rows.Count
Lb1.Clear
For rownumdist = startrowdist To totalrowdist
Lb1.AddItem Sheets(Trim(Me.Cb1.Value)).Cells(rownumdist, 1)
Lb1.List(rownumdist - startrowdist, 1) = Sheets(Trim(Me.Cb1.Value)).Cells(rownumdist, 2)
Lb1.List(rownumdist - startrowdist, 2) = Sheets(Trim(Me.Cb1.Value)).Cells(rownumdist, 3)
Lb1.List(rownumdist - startrowdist, 3) = Sheets(Trim(Me.Cb1.Value)).Cells(rownumdist, 4)
Lb1.List(rownumdist - startrowdist, 4) = Sheets(Trim(Me.Cb1.Value)).Cells(rownumdist, 5)
Lb1.List(rownumdist - startrowdist, 5) = Sheets(Trim(Me.Cb1.Value)).Cells(rownumdist, colnumdist)
Lb1.List(rownumdist - startrowdist, 6) = Sheets(Trim(Me.Cb1.Value)).Cells(rownumdist, colnumdist + 1)
Next rownumdist
Else
MsgBox ("Please Select Item First.")
End If
End Sub
Private Sub Lb1_Click()
startcoldist = 6
colqtydist = 2
colnumdist = (Me.Cb2.ListIndex * colqtydist) + startcoldist
startrowdist = 2
rownumdist = Me.Lb1.ListIndex + startrowdist
Tb4 = (Sheets(Trim(Cb1.Value)).Cells(rownumdist, 5))
Tb1 = (Sheets(Trim(Cb1.Value)).Cells(rownumdist, 2))
Tb2 = (Sheets(Trim(Cb1.Value)).Cells(rownumdist, 3))
Tb6 = (Sheets(Trim(Cb1.Value)).Cells(rownumdist, 4))
Tb5 = (Sheets(Trim(Cb1.Value)).Cells(rownumdist, colnumdist))
Tb3 = (Sheets(Trim(Cb1.Value)).Cells(rownumdist, colnumdist + 1))
End Sub