+ Reply to Thread
Results 1 to 6 of 6

Textbox values won't place into selected Listbox rows

Hybrid View

  1. #1
    Forum Contributor
    Join Date
    01-18-2010
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    127

    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:

    
    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?
    Attached Files Attached Files

  2. #2
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,259

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

    Hello Julesdude,

    There were no UserForms in the attached workbook. I suspect that CommandButton1 is on UserForm1. The first line of the button code unloads (destroys) UserForm1. You probably want to hide the form from view. The code below has some changes made that should help. Try the code and let know how it works out.
      UserForm1.Hide
      
      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.Offset(0, 3) > 0 Then
                c = c + 1
                For Ac = 1 To 12
                    Ray(c, Ac) = Dn.Offset(, -6 + Ac)
                Next Ac
            End If
        Next Dn
    
       UserForm2.Show
       
       With UserForm2.ListBox1
         .ColumnCount = 12
         .ColumnWidths = "20;35;70;120;0;55;55;55;50;40;45;200"
         .List = Ray
       End With
      
    End Sub
    Sincerely,
    Leith Ross

    Remember To Do the Following....

    1. Use code tags. Place [CODE] before the first line of code and [/CODE] after the last line of code.
    2. Thank those who have helped you by clicking the Star below the post.
    3. Please mark your post [SOLVED] if it has been answered satisfactorily.


    Old Scottish Proverb...
    Luathaid gu deanamh maille! (Rushing causes delays!)

  3. #3
    Forum Contributor
    Join Date
    01-18-2010
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    127

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

    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?

  4. #4
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,259

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

    Hello Julesdude,

    Please post your complete workbook: formulas, VBA code, UserForms, etc. I can then see what is causing the problem or problems with the user forms.

  5. #5
    Forum Contributor
    Join Date
    01-18-2010
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    127

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

    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?
    Attached Files Attached Files

  6. #6
    Forum Contributor
    Join Date
    01-18-2010
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    127

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

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

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1