No announcement yet.

Excel VBA Userform transfer information to spreadsheet

  • Filter
  • Time
  • Show
Clear All
new posts

  • Excel VBA Userform transfer information to spreadsheet

    Hi All,
    I am new to Ozgrid and relatively new to vba so sorry in advance if I am not explaining myself correctly. I am working on a project for my company with a competitors database. I have created the userform with text boxes, combo boxes, check boxes and one list box. By searching through the internet and talking with colleagues I have managed to do all the coding "correctly" and all the details are being transfered to the other spreadsheet except for the list box selection. I need the list box to be able to have multiple selections(Which already does) and put them in the allocated cell with a comma between each selection. This is the coding I have so far for the command add button:
     Private Sub CmdAdd_Click()
    'validate text box
    If Application.WorksheetFunction.CountIf(Worksheets("Full Competitor List").Range("D4:D250"), TxtName.Text) > 0 Then
        MsgBox ("Duplicate Competitor has been added. Please review Full Competitor List and delete accordingly!")
    End If
        'Copy input values to sheet.
        Dim lRow As Long
        Dim ws As Worksheet
        Set ws = Worksheets("Full Competitor List")
        lRow = ws.Cells(Rows.Count, 4).End(xlUp).Offset(1, 0).Row
        With ws
            .Cells(lRow, 4).Value = Me.TxtName.Value
            .Cells(lRow, 5).Value = Me.TxtPricing.Value
            .Cells(lRow, 6).Value = Me.TxtQuality.Value
            .Cells(lRow, 7).Value = Me.TxtDelivery.Value
            .Cells(lRow, 8).Value = Me.TxtProduct.Value
            .Cells(lRow, 9).Value = Me.TxtEcommerce.Value
            .Cells(lRow, 10).Value = IIf(CheckSample.Value, "Yes", "No")
            .Cells(lRow, 11).Value = IIf(CheckCAD.Value, "Yes", "No")
            .Cells(lRow, 12).Value = IIf(CheckManufacturers.Value, "Yes", "No")
            .Cells(lRow, 13).Value = IIf(CheckService.Value, "Yes", "No")
            .Cells(lRow, 14).Value = IIf(CheckCustom.Value, "Yes", "No")
            .Cells(lRow, 15).Value = Me.CboStrength.Value
            .Cells(lRow, 16).Value = Me.CboWeakness.Value
            .Cells(lRow, 17).Value = Me.TxtIndustries.Value
            .Cells(lRow, 18).Value = Me.ListProducts.Value
            .Cells(lRow, 19).Value = Me.TxtComments.Value
            .Cells(lRow, 20).Value = Me.DateBox.Value
        End With
        'Clear input controls.
        Me.TxtName.Value = ""
        Me.TxtPricing.Value = ""
        Me.TxtQuality.Value = ""
        Me.TxtDelivery.Value = ""
        Me.TxtProduct.Value = ""
        Me.TxtEcommerce.Value = ""
        Me.CheckSample.Value = ""
        Me.CheckCAD.Value = ""
        Me.CheckManufacturers.Value = ""
        Me.CheckService.Value = ""
        Me.CheckCustom.Value = ""
        Me.CboStrength.Value = ""
        Me.CboWeakness.Value = ""
        Me.TxtIndustries.Value = ""
        Me.ListProducts.Value = ""
        Me.TxtComments.Value = ""
    'Remember user before adding record
    MsgBox "The new competitor has been added", vbOKOnly, Verify
    'Close UserForm.
        Unload Me
    End Sub
    I have seen in some forums coding for userform listboxes but is just for the listbox by itself and I dont know how to integrate it toe the main subroutine(e.g. Can someone help me with the proper coding to integrate it in what I have already to add the selections of the listbox to the spreadhseet with a comma(,) between them.

    It would be much appreciated

  • #2
    Hi, without seeing your spreadsheet it's hard for me to give you an accurate response. However, assuming that your listbox was called ListProducts and you wanted that in column 18 then you would replace the line
    .Cells(lRow, 18).Value = Me.ListProducts.Value
    with the following:

        Dim i As Long
        Dim s As String
        If Me.ListProducts.Count > 0 Then
            For i = 0 To Me.ListProducts.ListCount
                If Me.ListProducts.Selected(i) Then
                    s = s & IIf(s = "", "", ",") & Me.ListProducts.List(i)
                End If
            Next i
        End If
        .Cells(lRow, 18).Value = s


    • #3
      Hi Trunten,

      Thank you very much for helping. You did make an accurate assumption, sorry for not attaching the spreadsheet. Would I put that code in the same "Private Sub CmdAdd_Click()" just below the rest, or literally replace that line in the code with new one or create a new sub?



      • #4

        Depending on where I try to put it in the code it gives me different error mesages, taken into account I already have the dim as part at the top would I need to open a new sub for the code that you posted?