List & Text Box Problem

[Hazel]

Hi All

I select the customer from a ListBox (Lb1) all the address data etc, enters
the Text Boxes OK. the ListBox is populated via RowSource. The ComboBox (Cb1)
is populated via "Add Item" on UserForm Initialize select an item from Cb1
and Tb6 and Tb7 fill Ok and can be entered on the sheet with all the other
data.

Private Sub Cb1_Click()
Sheets("Customers").Select
Dim iRow As Long
Dim ws As Worksheet
rownum = Lb1.ListIndex
UserForm1.Tb6.Value = Lb1.List(, 5)
UserForm1.Tb7.Value = Lb1.List(, 6)

End Sub

Is it possible to select another item in Cb1 using Tb6 & Tb7 as below or
would I need multiple TextBoxes

UserForm1.Tb6.Value = Lb1.List(, 7)
UserForm1.Tb7.Value = Lb1.List(, 8)

Hope I have made my problem an easy and understandable query.

 

[Halim]

hi,

Just guess your query:

try to use :
Private Sub Cb1_Change()
Tb6 = Lb1.list(Cb1.listindex , 6)
Tb7 = Lb1.list(Cb1.listindex , 7)
Tb8 = Lb1.list(Cb1.listindex , 8)
End sub

change Cb1.listindex to achieve your query.

 

[Hazel]

Hi Halim

The Change Event sets off the debugger because at the start TB6 is empty and
it appears I will have to have many textboxes because we have so many columns
to cover on the worksheet might have to go in a different direct to get this
working properly.

 

[Halim]

I'm not sure if my post give the correct answer as your query:

Private Sub Cb1_Change()
dim Tbx as textbox, i as long
for i = 6 to Lb1.ColumnCount
set Tbx = userform1.controls("Tb" & i)
Tbx.Value = Lb1.list(Cb1.listindex , i)
next i
End sub

 

[Hazel]

Hi Hallim

On Change Event the Userform goes in to DeBug -- On Click Event the UserForm
opens and all address data enters the textboxes as before however on making
a selection in Cb1

Private Sub Cb1_Click()

Dim Tbx As TextBox, i As Long
For i = 6 To Lb1.ColumnCount
Set Tbx = UserForm1.Controls("Tb" & i) '<<<<< Debugs on this line Runtime
Error 13 type mismatch
Tbx.Value = Lb1.List(Cb1.ListIndex, i)
Next i
End Sub

Also on Change Event debugs on same line with the same error message.

 

[Halim]

Ensure Tb is Textbox
or

Dim Tbx '''----> instead of Tbx as Textbox