excel - 如何为超过255个字符的范围创建验证列表

Question

最初我不知道为什么我的Excel文件不断损坏，但是经过大量研究，我意识到这是因为我的数据验证下拉列表包含超过255个字符。 “用逗号分隔的数据验证列表超过255个字符(包括逗号)将损坏工作簿”

这是我的代码:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Not Intersect(Target, Range("E12:E21")) Is Nothing Then

Dim col As New Collection
Dim rng As Range
Dim i As Long
Dim dvlist As String

'Loop thru the data range i.e. these are the cells used for the validation list. Very long list.
For Each rng In Sheet2.Range("B2:B249 , D2:D19")
'ignore blanks
    If Len(Trim(rng.Value)) <> 0 Then
        'create a unique list
        On Error Resume Next
        col.Add rng.Value, CStr(rng.Value)
        On Error GoTo 0
    End If
Next rng

'concatenate with "," as the delimiter
For i = 1 To col.Count
    dvlist = dvlist & col.Item(i) & ","
Next i

'add it to the DV
With Sheet1.Range("E12:E21").Validation
    .Delete
    .Add Type:=xlValidateList, _
    AlertStyle:=xlValidAlertStop, _
    Formula1:=dvlist
End With

End If


If Not Intersect(Target, Range("F12:F21")) Is Nothing Then

'Loop thru the data range i.e. these are the cells used for the validation list. Very long list.
For Each rng In Sheet2.Range("A2:A249")
'ignore blanks
    If Len(Trim(rng.Value)) <> 0 Then
        'create a unique list
        On Error Resume Next
        col.Add rng.Value, CStr(rng.Value)
        On Error GoTo 0
    End If
Next rng

'concatenate with "," as the delimiter
For i = 1 To col.Count
    dvlist1 = dvlist1 & col.Item(i) & ","
Next i

'add it to the DV
With Sheet1.Range("F12:F21").Validation
    .Delete
    .Add Type:=xlValidateList, _
    AlertStyle:=xlValidAlertStop, _
    Formula1:=dvlist1
End With


End If


End Sub

如您所见，我的列表很长，并且列表之间有空格，由于某些原因无法删除。因此，我无法在Excel中使用数据验证功能。有什么办法可以解决这个问题？

Answer 1

我自己研究了此问题后，发现最简单的方法实际上是在保存验证列表或将验证列表更改为255个字符以下之前删除验证列表。

Excel文件仅在保存并重新打开文件时损坏。以下是保存前删除验证列表的示例。

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

     CellWithValidationList.Validation.Delete

End Sub

当然，这假设您已经具有打开文件后在某些事件后会自动重新创建验证列表的VBA代码。