none
ListBox Populating Into Excel Sheet RRS feed

  • Question

  • Hi All,

    NOTE:  Row 1 On UserForm Should Populate In Row 7 In Worksheet "Capital"

               Row 2 On UserForm Should Populate In Row 8 In Worksheet "Capital"    

    I Have 2 Rows Of ListBoxes & TextBoxes & ComboBoxes. When A User Click On CommandButton1, My Code Populates Data From The UserForm Into An Excel Sheet Called "Capital". I Made A Code That Works And Does This Correctly...BUT lets Just Say A User Populates Just Row 2 On The UserForm And Not Row 1, The TextBoxes & Combo Boxes Populate Into The Correct Row (8) In "Capital" Sheet, BUT The ListBoxes Populate In Row (7) In "Capital" Sheet, Opposed To Row 2 Where It Was Populated In The UserForm. I'm Not Sure Why This Is Happening, I Tried Playing Around With My Code But Can't Figure It Out.

    Each Row In UserForm Looks Like This

    Row 1 On UserForm: TextBox1      ListBox1       TextBox2       ComboBox1        ListBox2    TextBox4

    Row 2 On UserForm:  TexxtBox5    ListBox3       TextBox6       ComboBox2        ListBox3    TextBox5  

    Below Is The Code:

    Private Sub CommandButton1_Click()

    Dim erow As Integer
    Dim ws As Worksheet
    Set ws = Worksheets("Capital")

    erow = ws.Cells(Rows.Count, 1).End(xlUp).Offset(3, 0).Row

    Me.TextBox1.SetFocus

    'Row1 On UserForm
    ws.Cells(7, 3).Value = Me.TextBox1.Value
    ws.Cells(34, 5).Value = Me.ComboBox1.Value
    ws.Cells(7, 6).Value = Me.TextBox4.Value

     'ListBox1
    Dim lngSelected As Long, lngRows As Long, lngColumn As Long
        Dim myArray(1 To 9)
       
        For lngSelected = 0 To Me.ListBox1.ListCount - 1
            If Me.ListBox1.Selected(lngSelected) Then
                lngRows = lngRows + 1
                For lngColumn = 1 To 9
                    myArray(lngColumn) = Me.ListBox1.List(lngSelected, lngColumn - 1)
                Next lngColumn
               ws.Cells(7, 4).End(xlUp).Offset(1).Resize(, 1) = myArray
            End If
    Next lngSelected

    'ListBox2
        For lngSelected = 0 To Me.ListBox2.ListCount - 1
            If Me.ListBox2.Selected(lngSelected) Then
                lngRows = lngRows + 1
                For lngColumn = 1 To 9
                    myArray(lngColumn) = Me.ListBox2.List(lngSelected, lngColumn - 1)
                Next lngColumn
               ws.Cells(7, 5).End(xlUp).Offset(1).Resize(, 1) = myArray
            End If
    Next lngSelected


    'Row2 On UserForm
    ws.Cells(8, 3).Value = Me.TextBox5.Value
    ws.Cells(35, 5).Value = Me.ComboBox2.Value
    ws.Cells(8, 6).Value = Me.TextBox8.Value
       
    'ListBox3
          For lngSelected = 0 To Me.ListBox3.ListCount - 1
            If Me.ListBox3.Selected(lngSelected) Then
                lngRows = lngRows + 1
                For lngColumn = 1 To 9
                    myArray(lngColumn) = Me.ListBox3.List(lngSelected, lngColumn - 1)
                Next lngColumn
               ws.Cells(8, 4).End(xlUp).Offset(1).Resize(, 1) = myArray
            End If
    Next lngSelected

    'ListBox4
         For lngSelected = 0 To Me.ListBox4.ListCount - 1
            If Me.ListBox4.Selected(lngSelected) Then
                lngRows = lngRows + 1
                For lngColumn = 1 To 9
                    myArray(lngColumn) = Me.ListBox4.List(lngSelected, lngColumn - 1)
                Next lngColumn
               ws.Cells(8, 5).End(xlUp).Offset(1).Resize(, 1) = myArray
            End If
    Next lngSelected

    End Sub

    Wednesday, March 28, 2018 2:45 PM

All replies

  • Using End(xlUp) will return a cell that depends on what is already in the cells above - So I would set the row once - you have a variable called "erow" that is not actually being used - and use it in place of all other End(xlUp) usages. 

    For example, change the four instance of this type of line:

    ws.Cells(7, 4).End(xlUp).Offset(1).Resize(, 1) = myArray

    to something like

    ws.Cells(erow, 4).Resize(, 1) = myArray

    Also, I'm not really sure why you are using an Array to return the values when all you are writing out to the sheet is the first value of the array into a single cell.


    Wednesday, March 28, 2018 8:04 PM