根据更改的Combobox值设置单元格vlookup值 [英] Set cell vlookup value based on changing Combobox value

问题描述

我在Excel工作表中输入了 combobox 。我想让它工作，以便没有访问 VBA 的用户可以从下拉菜单中选择一个值然后另一个单元格中的值将对此值执行 vlookup 。

I have input a combobox in an Excel sheet. I want it to work so that the user who does not have access to the VBA can select a value from the dropdown and then the value in another cell will perform a vlookup on this value.

在第一个例子中，我插入了一个框，并尝试基于此设置单元格值。

In the first instance I have inserted a box and am trying to set a cell value based on this.

Sub InsertComboBox()

#inserts dropdown box on the front page and sets the values as the list of DMA from the pipe_totals sheet
#this should be the most complete list so will not change dependant on asset

Dim arearange As Range
Set arearange = Sheets("pipe_totals").Range("a:a")
lastrowi = Application.WorksheetFunction.CountA(arearange)

Sheets("front page").Activate
With Range("f5:g5")
Set Combo = ActiveSheet.DropDowns.Add(.Left, .Top, .Width, .Height)

End With

Combo.List() = Sheets("pipe_totals").Range("A2:A" & lastrowi).Value
.Range("k9").Value = Combo.Value 'only works on current combobox value which is 0

End Sub

因此 vlookup 是动态的，取决于用户的选择？

Is there a way I can set this so the vlookup is dynamic depending on the users selection?

推荐答案

在此示例中，只需设置正确的组合名称。应该没问题，只要你的组合框列出了上面提到的Range（A2：A& lastrowi）的值。

In this example, just set the right combo name. It should be ok, provided that your combobox lists values from "Range("A2:A" & lastrowi)" as you mention above.

Sub "comboname"_Change()
    Dim list_val As Long

    list_val = Worksheets("front page").Shapes("comboname").ControlFormat.Value
    Range("K9") = Worksheets("pipe_totals").Cells((list_val + 1), 1)
End Sub

Sub test()
    Dim z As Shape
        For Each z In Worksheets("front page").Shapes
            Debug.Print z.Name
        Next z
End Sub

这篇关于根据更改的Combobox值设置单元格vlookup值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！