View Full Version : [SOLVED:] List Box Selection To Text Box
sooty8
08-30-2007, 08:35 AM
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
08-30-2007, 09:19 AM
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
but wouldn't a checkbos work better, or radio buttons?
sooty8
08-30-2007, 09:51 AM
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.:friends:
Bob Phillips
08-30-2007, 10:01 AM
This is untested, but perhaps you could work with this
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
sooty8
08-31-2007, 04:54 AM
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>>:dunno
Next i
End With
End Sub
any further idea's on how to solve this would be most appreciated
Cheers
Sooty8
Bob Phillips
08-31-2007, 05:45 AM
It works fine for me with the data I have setup (I have no idea what is in Lb1, so I invented it).
What is the value of aryItems(i) at the point the code errors and do you have a control by that name?
sooty8
08-31-2007, 06:24 AM
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:dunno
As that first bit made no sense to me, any chance you can post a sample? :)
sooty8
08-31-2007, 07:43 AM
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
And you definitely have textboxes called Tb9 and Tb10?
rbrhodes
09-02-2007, 03:28 AM
Hi All,
Playing with this...does this help or did I misunderstand?
sooty8
09-03-2007, 01:57 AM
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
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.