none
Combobox that references another worksheet RRS feed

  • Question

  • It's really hard to put this into the title alone. I have a worksheet that has a list of items and prices. On my main sheet, I want to have a combobox with all items from the other worksheet. When I select an item, it changes the adjacent cell to the price of that item. Then, I'd like to add/remove those comboboxes dynamically. I don't know how many items a user will need on a form. I'm struggling with creating event handlers. VB.NET was as easy as "AddHandler, AddressOf".

    Monday, June 13, 2016 7:30 PM

Answers

  • The true solution here is the following formula: =VLOOKUP($A19, ItemsPrices, 2, FALSE)

    I don't believe this needs to be a macro at all. I tried it without any code (and as a non-macro enabled workbook), and it worked great.

    You want the first argument to be the adjacent cell, that has your data validation. This is how Excel knows what the current selection is. The second argument should be a dynamic named range (or probably the raw name like A:B). ItemsPrices is just =Sheet2!$B$4:$C$45. The third argument says which column to use for matching. In my case, Prices were in column 2. Finally, the fourth argument is whether or not you want an exact or approximate match.

    I also wasn't able to get the subroutine to fire at all, on event. You'll need to add this.

    Private Sub Workbook_Open()
    Application.EnableEvents = True
    End Sub


    Tuesday, June 14, 2016 2:55 PM

All replies

  • Create a dynamic named range of your items, and use that named range as the source of a Data Validation dropdown for the cell of interest.

    In the adjacent cell, use a formula like

    =VLOOKUP(cell with DVList, Sheet2!A:B, 2, False)

    For the events:

    1) Copy this code.
    2) Right-Click the sheet tab of interest.
    3) Select "View Code"
    4) Paste the code into the window that appears.
    5) Save the file as a macro-enabled .xlsm file.
    6) Make changes as needed

    Private Sub Worksheet_Change(ByVal Target As Range)

        If Target.Cells.Count > 1 Then Exit Sub
        If Target.Value = "" Then Exit Sub
        'Change the range address to the range of interest
        If Intersect(Target, Range("A:A")) Is Nothing Then Exit Sub 'specific range

        Application.EnableEvents = False
        Application.Calculate ' just in case
        Target.Validation.Delete
        'optional to convert vllokup formula to value
        'Target.Offset(0, 1).Value = Target.Offset(0, 1).Value
        Application.EnableEvents = True
    End Sub

    Tuesday, June 14, 2016 2:03 PM
  • The true solution here is the following formula: =VLOOKUP($A19, ItemsPrices, 2, FALSE)

    I don't believe this needs to be a macro at all. I tried it without any code (and as a non-macro enabled workbook), and it worked great.

    You want the first argument to be the adjacent cell, that has your data validation. This is how Excel knows what the current selection is. The second argument should be a dynamic named range (or probably the raw name like A:B). ItemsPrices is just =Sheet2!$B$4:$C$45. The third argument says which column to use for matching. In my case, Prices were in column 2. Finally, the fourth argument is whether or not you want an exact or approximate match.

    I also wasn't able to get the subroutine to fire at all, on event. You'll need to add this.

    Private Sub Workbook_Open()
    Application.EnableEvents = True
    End Sub


    Tuesday, June 14, 2016 2:55 PM
  • "cell with Items" needs to be the cell address with the value of interest, like

    =VLOOKUP(B23,'Sheet with pricing'!B:C,2,False)

    So, if your descriptions are in column B, then in C23 enter that formula.  If they are in column E, then use E23 instead, in cell F23.

    And, to be clear, "Sheet with pricing" needs to be changed to the actual name of the sheet with the the cells B4:B45, and C4:C45 - or you can change the sheet name, but it needs to actually match.

    Building your formula by clicking and selecting ranges is usually the best choice. First, select the cell, and then type

    =VLOOKUP(

    then click the cell with the value, type a comma, click the other sheet and select columns B and C, then type

    , 2, False)

    and press then (and only then) press enter.


    Tuesday, June 14, 2016 3:32 PM
  • "The third argument says which column to use for matching."

    In VLOOKUP, the third argument is the column to pull data from.  The column for matching is always the first column of the range given as the second argument.

    Tuesday, June 14, 2016 3:52 PM