vba 是否可以增加 excel 验证下拉框中的 256 个字符限制？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/90365/
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
Is it possible to increase the 256 character limit in excel validation drop down boxes?
提问by Graham
I am creating the validation dynamically and have hit a 256 character limit. My validation looks something like this:
我正在动态创建验证并达到 256 个字符的限制。我的验证看起来像这样：
Level 1, Level 2, Level 3, Level 4.....
Is there any way to get around the character limit other then pointing at a range?
除了指向一个范围之外，有没有办法绕过字符限制？
The validation is already being produced in VBA. Increasing the limit is the easiest way to avoid any impact on how the sheet currently works.
验证已在 VBA 中生成。增加限制是避免对工作表当前工作方式产生任何影响的最简单方法。
回答by Graham
I'm pretty sure there is no way around the 256 character limit, Joel Spolsky explains why here: http://www.joelonsoftware.com/printerFriendly/articles/fog0000000319.html.
我很确定无法绕过 256 个字符的限制，Joel Spolsky 在这里解释了原因：http: //www.joelonsoftware.com/printerFriendly/articles/fog0000000319.html。
You could however use VBA to get close to replicating the functionality of the built in validation by coding the Worksheet_Change event. Here's a mock up to give you the idea. You will probably want to refactor it to cache the ValidValues, handle changes to ranges of cells, etc...
但是，您可以使用 VBA 通过编写 Worksheet_Change 事件来接近复制内置验证的功能。这是一个模型，可以给你这个想法。您可能希望重构它以缓存 ValidValues、处理对单元格范围的更改等...
Private Sub Worksheet_Change(ByVal Target As Range)
Dim ValidationRange As Excel.Range
Dim ValidValues(1 To 100) As String
Dim Index As Integer
Dim Valid As Boolean
Dim Msg As String
Dim WhatToDo As VbMsgBoxResult
'Initialise ValidationRange
Set ValidationRange = Sheet1.Range("A:A")
' Check if change is in a cell we need to validate
If Not Intersect(Target, ValidationRange) Is Nothing Then
' Populate ValidValues array
For Index = 1 To 100
ValidValues(Index) = "Level " & Index
Next
' do the validation, permit blank values
If IsEmpty(Target) Then
Valid = True
Else
Valid = False
For Index = 1 To 100
If Target.Value = ValidValues(Index) Then
' found match to valid value
Valid = True
Exit For
End If
Next
End If
If Not Valid Then
Target.Select
' tell user value isn't valid
Msg = _
"The value you entered is not valid" & vbCrLf & vbCrLf & _
"A user has restricted values that can be entered into this cell."
WhatToDo = MsgBox(Msg, vbRetryCancel + vbCritical, "Microsoft Excel")
Target.Value = ""
If WhatToDo = vbRetry Then
Application.SendKeys "{F2}"
End If
End If
End If
End Sub
