CODEWITHSUNDEEP
excelvbauserform
Phiking
Phiking

Reputation: 75

How to link each items in a combobox to a specific row?

I am creating a userform that is entering data on a form. And in that userform I have a Combobox that list out all the products. For each product there a row and the data that is being inputted in would only be on that row. 

Private sub cwbSave_Click()

Dim ws as Worksheet
Set ws = ActiveWorkbook.ActiveSheet

with ws
   Select Case cbProduct
      Case Is = "Apple"
          With ActiveSheet.Range("A14:P14")
                .Font.Bold = True
          End With
      ws.Cell(14,4) = Me.tbPrice
      ws.Cell(14,5) = Me.tbColor
      ws.Cell(14,6) = Me.tbSell
      Case Is = "Pineapple"
          With ActiveSheet.Range("A15:P15")
                .Font.Bold = True
          End With
      ws.Cell(15,4) = Me.tbPrice
      ws.Cell(15,5) = Me.tbColor
      ws.Cell(15,6) = Me.tbSell
   End Select
End With
End Sub

But the thing is, I got like 30 products. And it a lot of manually putting in. I was wondering if there an easier way to code this.

Thank you

Upvotes: 0

Views: 61

Answers (1)

Zac
Zac

Reputation: 1944

There are several ways to do this.. here is one:

In the UserForm_Initialize, add the below code:

Private Sub UserForm_Initialize()
    Dim aValues As Variant: aValues = WorksheetFunction.Transpose(ThisWorkbook.Worksheets("Sheet2").Range("A2:A5"))     ' Change sheet name and range to where your products are
    Dim iRow As Long

    ' Clear combobox
    Me.cmbCmbBox.Clear

    ' Fill combobox with the values from product range
    For iRow = LBound(aValues) To UBound(aValues)
        Me.cmbCmbBox.AddItem aValues(iRow)
    Next
End Sub

Above code uses your product range to populate the combobox. Now in cmbCmbBox_Change, add the following code:

Private Sub cmbCmbBox_Change()
    Dim oWS As Worksheet: Set oWS = ThisWorkbook.Worksheets("Sheet2")
    Dim rProdRange As Range: Set rProdRange = oWS.Range("A2:A5")
    Dim rItemRange As Range

    ' Find the selected item
    Set rItemRange = rProdRange.Find(Me.cmbCmbBox.Value)

    ' Set value in the sheet
    If Not rItemRange Is Nothing Then
        oWS.Cells(rItemRange.Row, 4) = Me.tbPrice
        oWS.Cells(rItemRange.Row, 5) = Me.tbColor
        oWS.Cells(rItemRange.Row, 6) = Me.tbSell
    End If

End Sub

You can add validation for when product is not found

Upvotes: 1

Related Questions