Listbox selection to fill Textboxes

autigers

Board Regular
Joined
Oct 9, 2005
Messages
139
After searching through and trying the various codes that were in this forum ... I have come up short ... (Shutup) :confused:
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
 

Some videos you may like

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

jindon

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995
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
Joined
Oct 9, 2005
Messages
139
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 .... :biggrin:

Respectfully,
JC
 

autigers

Board Regular
Joined
Oct 9, 2005
Messages
139
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
Joined
Aug 21, 2004
Messages
16,995

ADVERTISEMENT

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

Board Regular
Joined
Oct 9, 2005
Messages
139
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
Joined
Aug 21, 2004
Messages
16,995

ADVERTISEMENT

Where did debugger tell you? and which line?
 

autigers

Board Regular
Joined
Oct 9, 2005
Messages
139
Specified object cannot be found

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

jindon

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995
did you name added textbox as TextBox12?

or check if Sheet name "Waves" is correct.
 

autigers

Board Regular
Joined
Oct 9, 2005
Messages
139
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
 

Watch MrExcel Video

Forum statistics

Threads
1,118,448
Messages
5,572,166
Members
412,447
Latest member
immy
Top