none
Keep all listbox and textbox values from same userform on one row RRS feed

  • Question

  • I have a userform with a listbox and several textboxes.  I have the listbox populate and all but two of the textboxes autopopulate depending upon the selection from the listbox.  I have two textboxes where the user can add data.  I want to click a submit button and have all values sent to the same row, even if there is an empty cell above, everytime I click submit.

    Can anyone help me out?

    Wednesday, June 20, 2012 3:28 PM

Answers

  • "Inspector1.value" is from a multiselect listbox if that matters.

    Yes, it does matter. The Value property is not set in a multi select listbox. The following concatenates the selected values into one string so that they can be inserted into one cell. (If you wanted each selection in a cell of its own then need to know the criteria of how you want them to appear on the worksheet. ie. across the columns or down the rows.)

    If no selections are made in the MultiSelect ListBox then there will still be no value in the cell.

    Private Sub CommandButton1_Click()
        Dim Rab
        Dim i As Long
        Dim strList As String
       
        'Returning selected values from MultiList ListBox
        'and concatenate into one string
        With Me.Inspector1
            strList = ""
            For i = 0 To .ListCount - 1
              If .Selected(i) Then
                strList = strList & ", " & .List(i)
              End If
            Next i
        End With
       
        'Remove first comma from string
        If strList <> "" Then   'If "" then no selections made
            strList = Mid(strList, 2)
        End If
       
        With ThisWorkbook.Worksheets("MAGAZINE DATA")
            Rab = .Cells(.Rows.Count, 1).End(xlUp).Row + 1
            .Cells(Rab, 1).Value = Me.BuildingNumber1.Value
            .Cells(Rab, 3).Value = strList
            .Cells(Rab, 4).Value = Me.MagType1.Value
            .Cells(Rab, 5).Value = Me.LockType1.Value
            .Cells(Rab, 6).Value = Me.HaspType1.Value
        End With

    End Sub


    Regards, OssieMac

    • Marked as answer by lilmissfield22 Wednesday, June 27, 2012 11:08 AM
    Wednesday, June 27, 2012 3:23 AM

All replies

  • Private Sub CommandButton1_Click()
        Dim R As Long

        With Workbooks("FileName.xls").Worksheets("Sheet Name")
            R = .Cells(.Rows.Count, 1).End(xlUp).Row + 1
            .Cells(R, 1).Value = Me.TextBox1.Text
            .Cells(R, 2).Value = Me.TextBox2.Text
            .Cells(R, 3).Value = Me.TextBox3.Text
            .Cells(R, 4).Value = Me.TextBox4.Text

    'Etc.

        End With

    End Sub


    HTH, Bernie

    Wednesday, June 20, 2012 4:50 PM
  • Thanks for that. It works great. 
    Wednesday, June 20, 2012 5:34 PM
  • Dim Rab As Long
     
        With Workbooks("NEW MAG DATABASE TEST ONLY 444.xlsm").Worksheets("MAGAZINE DATA")
             Rab = .Cells(.Rows.Count, 1).End(xlUp).Row + 1
             .Cells(Rab, 1).Value = Me.BuildingNumber1.Value
             .Cells(Rab, 3).Value = Me.Inspector1.Value
             .Cells(Rab, 4).Value = Me.MagType1.Value
             .Cells(Rab, 5).Value = Me.LockType1.Value
             .Cells(Rab, 6).Value = Me.HaspType1.Value
             
             End With

    For some reason this is skipping column 3. Any clue how to fix it? "Inspector1.value" is from a multiselect listbox if that matters.
    Tuesday, June 26, 2012 12:49 PM
  • "Inspector1.value" is from a multiselect listbox if that matters.

    Yes, it does matter. The Value property is not set in a multi select listbox. The following concatenates the selected values into one string so that they can be inserted into one cell. (If you wanted each selection in a cell of its own then need to know the criteria of how you want them to appear on the worksheet. ie. across the columns or down the rows.)

    If no selections are made in the MultiSelect ListBox then there will still be no value in the cell.

    Private Sub CommandButton1_Click()
        Dim Rab
        Dim i As Long
        Dim strList As String
       
        'Returning selected values from MultiList ListBox
        'and concatenate into one string
        With Me.Inspector1
            strList = ""
            For i = 0 To .ListCount - 1
              If .Selected(i) Then
                strList = strList & ", " & .List(i)
              End If
            Next i
        End With
       
        'Remove first comma from string
        If strList <> "" Then   'If "" then no selections made
            strList = Mid(strList, 2)
        End If
       
        With ThisWorkbook.Worksheets("MAGAZINE DATA")
            Rab = .Cells(.Rows.Count, 1).End(xlUp).Row + 1
            .Cells(Rab, 1).Value = Me.BuildingNumber1.Value
            .Cells(Rab, 3).Value = strList
            .Cells(Rab, 4).Value = Me.MagType1.Value
            .Cells(Rab, 5).Value = Me.LockType1.Value
            .Cells(Rab, 6).Value = Me.HaspType1.Value
        End With

    End Sub


    Regards, OssieMac

    • Marked as answer by lilmissfield22 Wednesday, June 27, 2012 11:08 AM
    Wednesday, June 27, 2012 3:23 AM
  • That works perfectly. Thank you sooooo much.
    Wednesday, June 27, 2012 11:08 AM