# Listbox selection to fill Textboxes

#### autigers

##### Board Regular
After searching through and trying the various codes that were in this forum ... I have come up short ... (Shutup) I have a listbox on userform that populates data from the code below
what I am trying to achieve is that when I select a line in the listbox, I wish to have the 11 textboxes that are under the listbox populate with the data that relates to the list box for editing purposes ....
As always your assistance is greatly appreciated ....
Code:
``````Private Sub UserForm_Initialize()
Dim a, i As Long, ii As Integer, n As Long
Dim ListAry()
With Sheets("Waves").Range("A2").CurrentRegion
a = .Resize(, 13).Value
End With
For i = 2 To UBound(a, 1)
If IsEmpty(a(i, 13)) Then
n = n + 1
ReDim Preserve ListAry(1 To 13, 1 To n)
For ii = LBound(a, 2) To UBound(a, 2)
If ii = 2 Then
If Not IsEmpty(a(i, ii)) Then
ListAry(ii, n) = CStr(Format(a(i, ii), "hh:mm AM/PM"))
End If
Else
ListAry(ii, n) = a(i, ii)
End If
Next
ListAry(13, n) = i
End If
Next
Erase a
If n = 0 Then Me.ListBox1.Clear: Exit Sub
With Me.ListBox1
.ColumnCount = 12
.ColumnWidths = "0;52;54;54;54;54;54;54;54;54;54;40;10"
.Column = ListAry
End With
End Sub``````

### Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

#### jindon

##### MrExcel MVP
assuming textbox names are 1 to 11
Code:
``````Private Sub ListBox1_Click()
Dim i As Integer, a()
With Me.ListBox1
For i = 0 To .ListCount - 1
If .Selected(i) Then
For ii = 1 To .ColumnCount - 1
ReDim Preserve a(n)
a(n) = .List(i, ii)
n = n + 1
Next
End If
Next
End With
For i = 1 To 11
Me.Controls("TextBox" & i) = a(i - 1)
Next
End Sub``````

#### autigers

##### Board Regular
jindon ..... once again the listbox king .... Works like a charm ....

You have been the author of now 6 different codes in the wb !!!!

As always thank you for you assistance ....

FYI .. Getting close to completion .... Respectfully,
JC

#### autigers

##### Board Regular
jindon, that works for populating the text boxes ... but how I set my code up to make changes to the record ?

Code:
``````Private Sub btn_OK_Click()

Dim i As Integer
With Me.ListBox1
For i = 0 To .ListCount - 1
If .Selected(i) Then
Sheets("Waves").Range("m" & .List(i, 14)) = Me.CheckBox2.Value
Exit For
End If
Next
End With
Me.CheckBox2.Value = False
UserForm_Initialize
End Sub``````

#### jindon

##### MrExcel MVP

add one more textbox(12) and make its width 0, so that you cannot see it.
change the code
Code:
``````Private Sub ListBox1_Click()
Dim i As Integer, a()
With Me.ListBox1
For i = 0 To .ListCount - 1
If .Selected(i) Then
For ii = 1 To .ColumnCount
ReDim Preserve a(n)
a(n) = .List(i, ii)
n = n + 1
Next
End If
Next
End With
For i = 1 To 12
Me.Controls("TextBox" & i) = a(i - 1)
Next
End Sub``````
for i=1 to 11
sheets("Waves").cells(me.textbox12,i+1)=me.controls("Text
Box & i)
next
[/code]
see if it works

#### autigers

##### Board Regular
okay ...heres what I have ... but keeps error

Code:
``````Private Sub CommandButton2_Click()
For i = 1 To 11
Sheets("Waves").Cells(Me.TextBox12, i + 1) = Me.Controls("TextBox & i")
Next
End With
End Sub``````

Code:
``````Private Sub UserForm_Initialize()
grpWaveCarryover.Visible = False

Dim a, i As Long, ii As Integer, n As Long
Dim ListAry()
With Sheets("Waves").Range("A2").CurrentRegion
a = .Resize(, 13).Value
End With
For i = 2 To UBound(a, 1)
If IsEmpty(a(i, 13)) Then
n = n + 1
ReDim Preserve ListAry(1 To 13, 1 To n)
For ii = LBound(a, 2) To UBound(a, 2)
If ii = 2 Then
If Not IsEmpty(a(i, ii)) Then
ListAry(ii, n) = CStr(Format(a(i, ii), "hh:mm AM/PM"))
End If
Else
ListAry(ii, n) = a(i, ii)
End If
Next
ListAry(13, n) = i
End If
Next
Erase a
If n = 0 Then Me.ListBox1.Clear: Exit Sub
With Me.ListBox1
.ColumnCount = 12
.ColumnWidths = "0;52;54;54;54;54;54;54;54;54;54;40;10"
.Column = ListAry
End With

End Sub``````

#### jindon

##### MrExcel MVP

Where did debugger tell you? and which line?

#### autigers

##### Board Regular
Specified object cannot be found

Code:
``Sheets("Waves").Cells(Me.TextBox12, i + 1) = Me.Controls("TextBox & i")``
[/img]

#### jindon

##### MrExcel MVP
did you name added textbox as TextBox12?

or check if Sheet name "Waves" is correct.

#### autigers

##### Board Regular
Yes to both.
When I select a line in the listbox textbox12 shows a number 2 or 3 since there are only 2 entries listed.

Sheet "Waves" is present and correctly spelled Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

### Forum statistics

1,163,998
Messages
5,834,802
Members
430,322
Latest member
excelnoobnoob ### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?    1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option. Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com". Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button. Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button. Go back