Search code examples
excelvbatextbox

Enter specific text into textbox and add +1 to another cell

I'm new to excel but now i'm stuck at something. The one thing i try to achieve is that if i add a specific word into a textbox, another cell gets +1 (so if 0 and text has been entered in the textbox, it changes to 1 etc.) so for example:

Cell B2 = Apple

Cell H2 : value of B2

I'd like to get, if possible, one or two textboxes where i could put the type of product and another box for the amount.

Thanks in advance.

Solution

  • OK. Here's a solution.

    1. Set up a named range. I entered 5 different fruit in one column and 5 different quantities in the adjacent column (doesn't matter where but must be adjacent). I named the range "Products" but any other name will do just as well.
    2. I set up a data validation list. I used cell G3 but any other will be equally suitable. I pointed the data validation list to =INDEX(Products,0,1), meaning the first column of the Products range.

    3. Now I added code to the worksheet. This code must be in the code sheet of the worksheet on which G3 is located. That code sheet will have a name like Sheet1 (Sheet1). Don't use a standard code module with a name like Module1. Here is the code.

      Private Sub Worksheet_Change(ByVal Target As Range) ' 018

      Const Trigger = "G3"                ' change to suit
Dim Qty As Long

With Target
    If .Address = Range(Trigger).Address Then
        On Error Resume Next            ' in case not found
        Qty = Application.VLookup(.Value, Range("Products"), 2, False)
        .Offset(0, 1).Value = Qty + 1
    End If
End With

      End Sub

    Note that the Trigger cell is named as "G3". This must be the cell where you have the data validation drop-down.

    This code will run whenever Trigger is changed. You make a selection there and the VLOOKUP function will find the quantity in column 2 of the Products range. If the item isn't found in the list it will return 0 but you can set the cell validation to prevent the entry of an item that isn't in the list. The code will add one to the quantity found and issue the result in .Offset(0, 1), meaning one column to the right of the Trigger cell.

    You might want to do other things with your idea. I think the system I suggest can be adapted to whatever you have in mind, including changing the quantity in the Products list.