Excel VBA Userform with combo box and Vlookup

Question

I try to make my first UserForm with combo box, I already made this:

   Private Sub cmdClose_Click()
   Unload Me
   End Sub

  Private Sub Reg1_AfterUpdate()
  If WorksheetFunction.CountIf(Stock.Range("A:A"), Me.Range.Value) = 0 Then
  NsgBox "This is an incorrect item"
  Me.Reg1.Value = ""
  Exit Sub
  End If

  With Me
  .Reg2 = Application.WorksheetFunction.VLookup(CLng(Me.Reg1), 
  Stock.Range("Lookup"), 2, 0)
  End Sub 

  Private Sub UserForm_Click()
  End Sub

The thing is that I don't have any idea how to use combo box with Vlookup function.

I need a combo box because I would like to change a number on Sheet1.

For example:

       Harry      10
       David      20

  A1 Harry   B1  10
  A2 David   B2  20

So I would like to select a name from a Combo Box. After I selected a name I would like to type in a number into a textbox and this number is going to belong for the selected name and sum up with the existing number.

So Harry has 10. After I selected Harry from combo box and set 90 in TextBox the number is going to change for 100 for Harry. That's why I think I have to use Vlookup somehow in VBA.

Thank you

Teasel Teasel · Accepted Answer · 2017-10-09T19:17:45

The code below should work as you asked

Sub ChangeValue()
    Dim sheetName As String

    sheetName = "Name of your sheet"

    With ThisWorkbook.Sheets(sheetName)
       'For each name in your range
        For Each cell In .Range("Your range where names are") 
           'If the name is the one selected in your combobox
            If (cell = YourComboBox.Text) Then
                'Increment value 
                .Range(cell.Address).Offset(0, 1).Value = _
                .Range(cell.Address).Offset(0, 1).Value + YourTextBoxValue.Text
            End If
        Next cell
    End With
End Sub

Usage

Replace Name of your sheet with the name of the sheet where the names are.

Replace Your range where names are with the range where we can find all the names in your sheet.

Replace YourComboBox and YourTextBoxValue with the names of your components in your userForm.

Excel VBA Userform with combo box and Vlookup

1 Answers

Usage