具有动态范围的下拉列表的 VBA 代码
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/42282298/
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
VBA Code for drop-down list with dynamic range
提问by S.Katiyar
I am trying to write a macro for multiple drop-downs in "n" cells (let's say 100) in a column. The ranges(drop-down values) for these drop-downs have to be picked from a table with same number of rows (100 in our case).
我正在尝试为列中的“n”个单元格（假设为 100）中的多个下拉列表编写宏。这些下拉列表的范围（下拉值）必须从具有相同行数（在我们的示例中为 100）的表中选取。
I am unable to run the for loop for the formula part (highlighted below). I want the macro to pick D2:H2 range for i=2, D3:H3 for i=3, and so on. How do I do it? Is there any alternative to this?
我无法为公式部分运行 for 循环（在下面突出显示）。我希望宏为 i=2 选择 D2:H2 范围，为 i=3 选择 D3:H3，依此类推。我该怎么做？有什么替代方法吗？
Looking forward to valuable inputs. Thanks!!
期待有价值的投入。谢谢！！
Sub S_Dropdown3()
Dim wks As Worksheet: Set wks = Sheets("Sheet1")
wks.Select
Dim i As Integer
For i = 2 To 101
With Range("B" & i).Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, **Formula1:="=Sheet2!D2:H2"**
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
Next i
End Sub
回答by Ralph
The following code should work:
以下代码应该可以工作：
Option Explicit
Sub S_Dropdown3()
Dim wks As Worksheet
Dim i As Integer
Set wks = ThisWorkbook.Worksheets("Sheet1")
wks.Activate
For i = 2 To 101
With wks.Range("B" & i).Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=Sheet2!D" & i & ":H" & i
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
Next i
End Sub
Implemented changes:
已实施的更改：
- Code formatting / indentation
- Implementing full qualification to ensure that
Sheet1refers toSheet1in the workbook from which the macro is run (in case that more than one Excel file is open). - Sheets cannot be
.Selectedonly ranges get selected. Sheets can only be.Activated. Earlier versions of Excel don't mind. Never versions of Excel will throw an error with that line. - Fully qualifying
.Range("B" & i). - Finally, making the formula modular as requested in the initial post.
- 代码格式/缩进
- 实施完全限定以确保在运行宏的工作簿中
Sheet1引用Sheet1（以防打开多个 Excel 文件）。 - 工作表不能
.Selected只选择范围。表只能是.Activated. 早期版本的 Excel 不介意。Excel 的任何版本都不会在该行中引发错误。 - 完全出线
.Range("B" & i)。 - 最后，按照初始帖子中的要求使公式模块化。
