list - VBA 验证列表错误

Question

我对 vba 编程很陌生。我需要一些编码帮助，在我尝试谷歌搜索解决方案后我无法找到解决方案。

目前我有一个名为类别映射的自定义类型。该类型将用于包含我想作为 vadliation 列表选项放置的项目。它看起来如下:

Public Type categoryMapping
    messageKey As Long
    description As String
End Type

categoryMapping 的一个例子是存储性别代码，6000 代表男性，6001 代表女性。

验证列表的显示
由于我都将它们存储在一个数组中，因此显示它们并不容易。我所做的如下:

'Validation drop down list for the whole row
If Has_Elements(mapping) Then
    Dim code As String
    Dim options() As categoryMapping

    code = ""
    options = mapping

    Dim j As Integer
    For j = LBound(options) To UBound(options)
        code = code & options(j).messageKey & ": " & options(j).description & ","
    Next j

    With Range(Rows(7).Address).Validation    'TODO: Need to refactor
        .Add Type:=xlValidateList, _
        AlertStyle:=xlValidAlertStop, _
        Operator:=xlBetween, _
        Formula1:=code
        .InCellDropdown = True
        .InputMessage = "Please choose from of the following"
        .ShowInput = True
    End With
End If

由于我已经将所有要显示的项目放入数组中，因此是否更容易显示它们？我可以直接调用数组吗？

使用单元格
从上面的代码可以看出，我已经使用整行的地址来包含验证列表，因为实际上我想要的是整行，除了标题单元格来包含验证列表。

With Cells(7,1).Validation    'TODO: Need to refactor
    .Add Type:=xlValidateList, _
    AlertStyle:=xlValidAlertStop, _
    Operator:=xlBetween, _
    Formula1:=code
    .InCellDropdown = True
    .InputMessage = "Please choose from of the following"
    .ShowInput = True
End With

我尝试使用上面的代码来做，但它失败了。有什么办法可以去做吗？

合并多个验证删除

由于上面提到的问题，我需要输入代码来删除某些列的验证列表，如下图:

'Delete the unneccessary validation
'TODO: refactor the code so that write in 1 line
Range(Columns(1).Address).Validation.Delete
Range(Columns(2).Address).Validation.Delete
Range(Columns(3).Address).Validation.Delete
Range(Columns(4).Address).Validation.Delete

有什么方法可以将所有删除验证合并到一个语句中？

显示部分选项

从上面可以看出，当我的用户从验证列表中选择一个选项时，整个字符串都会出现。

例如，如果我有“6000:男”和“6001:女”作为选项，并且我选择男，我希望出现“6000”而不是“6000:男”。有没有办法做到这一点？

重新打开后验证数据不存在

生成验证列表后，我关闭程序并重新打开它，出现错误提示“Excel 在 'File name.xls' 中发现不可读的内容。您要恢复此工作簿的内容吗？如果您信任来源在此工作簿中，单击是。

当我单击是时，我的 excel 打开，但我的所有验证列表都消失了!我收到以下错误消息。

- error064240_01.xml Errors were detected in file 'file_name' - Removed Feature: Data validation from /xl/worksheets/sheet2.xml.part

我猜测发生错误是因为验证列表中的选项没有存储在工作表中，而是存储在程序内存中，因此当我关闭程序时，内存丢失了。

如果我的猜测是正确的，有没有办法解决这个问题？我正在考虑创建另一个包含验证列表中所有数据的工作表，并让我的单元格验证列表引用它们，但是有更好的方法将它们存储在同一个工作表中吗？

Answer 1

不可读的内容是因为您将验证列表存储在内存中的数组中。关闭工作表后，该数组就会超出范围并不再存在。我通常不会将其存储在数组中，而是将列表写入某个工作表，而是创建一个名为列表的新工作表。

您的单元格的实际验证不会使用 VBA。只需创建一个动态命名范围(使用 offset() 和 counta() 从列表表中的相关列填充您的验证列表。然后使用您的 VBA 代码将上面的数组写入该列。(http://chandoo.org/wp/2010/09/13/dynamic-data-validation-excel/)

至于在验证和下拉列表中显示不同的文本，这听起来比它的值(value)更麻烦。是否可以仅调整单元格的大小，使其仅显示前 4 位数字？