Listbox selection to fill Textboxes

[autigers]

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 ....

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

 

[jindon]

assuming textbox names are 1 to 11

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]

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]

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

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]

add one more textbox(12) and make its width 0, so that you cannot see it.
change the 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

add one command button then
for i=1 to 11
sheets("Waves").cells(me.textbox12,i+1)=me.controls("Text
Box & i)
next
[/code]
see if it works

 

[autigers]

okay ...heres what I have ... but keeps error

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

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]

Where did debugger tell you? and which line?

 

[autigers]

Specified object cannot be found

Sheets("Waves").Cells(Me.TextBox12, i + 1) = Me.Controls("TextBox & i")

[/img]

 

[jindon]

did you name added textbox as TextBox12?

or check if Sheet name "Waves" is correct.

 

[autigers]

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