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:
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:
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?
Bookmarks