How to take insert data (numbers) from a combobox into a spreadsheet underneath the column specified by a different combobox, which is a text? RRS feed

  • Question

  • Is this possible? For instance, I have one combobox in which the user selects a customer, and another combobox where the user enters a part number. The customer names are in the first row of a sheet and I want the part numbers to appear below that customer name chosen, by smallest to largest and underneath the already present part #'s. As of now I have this, but I know it's wrong.
            idx = ComboBox1.Text
            Set rngParts = .Range(.Cells(2, idx + 1), .Cells(LastRow, idx + 1))
            With Sheets("Metallize")
                Unload NewPartNumUF
                RowNum = .Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
                Cells(RowNum, idx) = TextBox1.Value
                Unload NewPartNumUF
            End With

    Friday, May 10, 2013 2:43 PM


  • i made a program to do exactly what you want, but totally not based on your program,

    it is for a userform where Ainvoer = a button to add an input, Ninvoer a textbox where you can write your input and invoer the combobox where the input is collected; then you have the same procedure for the columns: Aplaats is the button to add, Nplaats the textbox to write and plaats the combobox; then you have the button start that starts the procedure to do what you wanted.

    Private Sub Ainvoer_Click() If Ninvoer.Text = "" Then ' this is a control to make sure that there is an input MsgBox "no input", vbOKOnly, "mistake" Else invoer.AddItem (Ninvoer.Text) ' here you add the input to the first combolist Ninvoer.Text = "" ' and you set the input- textbox equal to "" End If End Sub

    Private Sub Aplaats_Click() If Nplaats.Value < 1 Or Nplaats.Value > 100 Then ' a control to make sure that the value is where you want it, you can of course MsgBox "no correct input", vbOKOnly, "mistake" ' change the 100 to another number Else plaats.AddItem (Nplaats.Text) ' and again you add the input to the combobox Nplaats.Text = "" End If End Sub

    Private Sub Start_Click() If plaats.Text = "" Then 'first you check if there is a column selected MsgBox "please select a column", vbOKOnly, "mistake" Exit Sub End If For i = 0 To invoer.ListCount - 1 'you make a loop from 0 (because the first element of the combobox is with index 0 Cells(i + 1, CInt(plaats.Value)) = invoer.List(i) 'to the number of items - 1 (same reason); you use for the cell-co's i + 1 because Next 'you haven't got a cell 0 of course, and that's it! End Sub

    get it?

    • Proposed as answer by Mr. Rik Saturday, May 11, 2013 9:40 AM
    • Edited by Mr. Rik Sunday, May 12, 2013 10:13 AM
    • Marked as answer by Damon ZhengModerator Thursday, July 4, 2013 1:20 PM
    Saturday, May 11, 2013 9:39 AM