Thread: Textbox values won't place into selected Listbox rows

Hello can anyone help? I have an issue with a listbox, although I've had some help with it so far, I can't quite tie this up.

What I need Excel to do is bring up a userform with a listbox and three textboxes. The listbox filters rows in the worksheet, revealing an entire row only if a value in column J is less than zero.

When the user selects the row in the listbox and enters something in each textbox, upon clicking the 'ok' commandbutton:
For that row in the worksheet, insert a row underneath with no formatting.
In that new blank row:
Put textbox1 value in cell in column E
Put textbox2 value in cell in column D
Put textbox3 value in cell in column I

I've attached a sample worksheet with the data I'm working on.

Here's the code I have so far for filtering rows and displaying in the listbox:

Code:

Private Sub Commandbutton1_Click()
Unload UserForm1
Dim Rng As Range, Dn As Range, Ray, c As Long, Ac As Integer
Set Rng = Range(Range("G6"), Range("G" & Rows.Count).End(xlUp))

ReDim Ray(1 To Rng.Count, 1 To 12)

    For Each Dn In Rng
        If Dn.Value <> "" And Dn.Next.Next.Next > 0 Then
            c = c + 1
            For Ac = 1 To 12
                Ray(c, Ac) = Dn.Offset(, -6 + Ac)
            Next Ac
        End If
    Next Dn

With UserForm2.ListBox1
    .ColumnCount = 12
    .ColumnWidths = "20;35;70;120;0;55;55;55;50;40;45;200"
    .List = Ray
 End With

UserForm2.Show
End Sub

Then, i've entered this code clicking on the commandbutton ok in the userform editor. This tries to enter the textbox info into the row selected:

Code:

Private Sub commandbutton1_Click()
Dim Rng As Range, Dn As Range
Dim n As Integer, Rw As String, Ac As Integer
Set Rng = Range(Range("J5"), Range("J" & Rows.Count).End(xlUp))
With UserForm2.Listbox1
For n = 0 To .ListCount - 1
    If .Selected(n) Then
       For Ac = 0 To .ColumnCount - 1
            If IsDate(.Column(Ac, n)) Then
                Rw = Rw & CDbl(DateValue(.Column(Ac, n))) & ","
            Else
                Rw = Rw & ListBox1.Column(Ac, n) & ","
            End If
      Next Ac
      Exit For
    End If
 Next n
End With
If Rw <> "" Then
    Rw = Left(Rw, Len(Rw) - 1)
    For Each Dn In Rng
        If Rw = Join(Application.Transpose(Application.Transpose(Dn.Resize(, 12))), ",") Then
            Range("E" & Dn.Row) = Textbox1.Value
            Range("D" & Dn.Row) = Textbox2.Value
            Range("I" & Dn.Row) = Textbox3.Value
        End If
Next Dn
End If
End Sub

Whilst the first part code works fine and lists what I need it to for the listbox, the commandbutton, when clicked, just does nothing and I don't know why. Perhaps because the commandbutton is part of a different sub in a different section and these two codes are not passing instructions on to one another.

Can anyone help?

Hi Leith, thanks for getting back to me.

I tried your amendment but still with little joy. In fact though hiding userform1 instead of unloading it was fine, I ran in to problems putting the userform2.show command earlier than I had originally. So I ended up getting a completely blank userform2 listbox. I've had to revert back to the original code for the moment.

Just to make clear the userforms. Userform1 is a set of command buttons connecting other user forms which allow the user to modify the worksheet. Userform2 is one of those userforms and contains listbox1 (first code I posted) that lists rows which meet certain conditions (if value in column J is greater than 0 then list that row).

Also in userform2 are 3 text boxes. These text boxes must be put into a newly inserted row underneath the one selected by the user in the listbox.

I was hoping that in the second code I posted, the text box text would be input into the corresponding blank row inserted underneat. But it does nothing when I click on the commandbutton for userform2.

What am I doing wrong?

Hey Leith, I've attached an example here that I'm working on. It's Userform2 that I'm having difficulty with. Userform3 uses the same kind of code, uses one textbox and it works fine. But somehow, for some reason the same code will not work for the 3 textboxes on Userform2 to go into the corresponding row as selected in the listbox.

Any idea what's going wrong?

....forgot to say...the textbox values need to go into a newly inserted line that is created upon the commandbutton click. This new line should not carry the cell background shading (borders are ok though) from the above line. That above line will have been listed in the listbox and selected by the user. Somehow I need to figure a way of doing this.