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
 

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
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
 
Master Excel Bundle

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

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top