问题描述
我在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值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!