vba 根据更改的 Combobox 值设置单元格 vlookup 值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/27962157/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Set cell vlookup value based on changing Combobox value
提问by squar_o
I have input a comboboxin an Excel sheet. I want it to work so that the user who does not have access to the VBAcan select a value from the dropdownand then the value in another cell will perform a vlookupon this value.
我combobox在 Excel 表格中输入了 a 。我希望它能够工作，以便无权访问 的用户VBA可以从 中选择一个值dropdown，然后另一个单元格中的值将vlookup对该值执行 a 。
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
Is there a way I can set this so the vlookupis dynamic depending on the users selection?
有没有一种方法可以设置它，以便vlookup根据用户的选择动态变化？
回答by dimitris
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.
在这个例子中，只需设置正确的组合名称。应该没问题，前提是您的组合框列出了您上面提到的“Range("A2:A" & lastrowi)”中的值。
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
回答by dimitris
As far as I understand, you want that everytime the combobox value changes, cell K9 will have the same value also. Is that right? If this the case, then right click on the combobox and select "Assign Macro". Then select "Create". Then inside the sub created, which should look like this:
据我了解，您希望每次组合框值更改时，单元格 K9 也将具有相同的值。那正确吗？如果是这种情况，请右键单击组合框并选择“分配宏”。然后选择“创建”。然后在创建的子里面，它应该是这样的：
Sub "comboname"_Change()
End Sub
You should also paste the final code line.
您还应该粘贴最后的代码行。
.Range("k9").Value = Combo.Value
Doing so, means you want that line of code executed every time the combobox value changes.
这样做意味着您希望每次组合框值更改时都执行该行代码。
