Excel VBA：命名范围 +字符串值作为验证列表？
好吧，基本上我想做的是为特定单元格设置数据验证列表。 该列表应包含我指定的一定范围的单元格以及添加到其中的字符串值。 我有一个对话框要求用户输入名称，然后我需要显示包含一组带有“其他：”和“其他：”的单元格范围的列表。添加了名称。
name = "Test"
With Worksheets("Tijdsregistratie").Cells(aangepasteRij, 4).Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=nameRange, name"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
类似于上面的代码（显然该公式不起作用）。 有没有办法使用特定的公式来做到这一点，或者我是否必须找到另一种方法来完成这项工作？
Alright, basically what I want to do is set a data validation list for a specific cell.
This list should contain a certain range of cells I've specified PLUS a string value added to it.
I have a dialog box that asks the user for a name, and then I need the list to display containing a set range of cells with "Other: " & Name added to it.
name = "Test"
With Worksheets("Tijdsregistratie").Cells(aangepasteRij, 4).Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=nameRange, name"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
Something like the above code (obviously the formula doesn't work).
Is there any way to do this using a specific formula, or am I going to have to find another way to get this done?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
数据验证不接受“范围并集”（例如
"=nameRange, name"或"=$I$4:$I$9;$A$21"将是无效输入），但是当您更新对话框时，您可以创建一个包含“其他”和名称的新范围，并针对此新范围进行验证，或者仅在验证范围的末尾附加对话框。检查网络我设备这个快速黑客（下面的示例）
正如您所看到的，公式1接受格式为“选项1，选项2，选项3”的字符串输入，所以这很简单：使用您的验证范围构建一个字符串并附加您的数据对话框。
A data validation doesn´t accept "union of ranges" (by example
"=nameRange, name"or"=$I$4:$I$9;$A$21"would be a invalid input), but when you update the dialog box you can create a new range that include "Other "&Name and validate against this new range, or just append at the end of the range of validation the result of the dialog box.Checking the web I device this quick hack (example below)
As you can see Formula1 accept string input in the format "option1,option2,option3", so this is easy: build a string with you validation range and append you your data from the dialog box.