通过 VBA 更新损坏的数据验证列表-解网

问：

我有一个电子表格，其中包含几个验证列表，其中一个链接到条件格式以识别记录的状态，“已关闭”、“打开”、“待处理”等。sptreadsheet 相当大，并且随着时间的推移删除和插入行，某些行的条件格式状态数据验证列表会被破坏。

我的手动清理过程非常耗时，我正在尝试通过 vba 以编程方式重新创建损坏的行。此代码片段有效，但其是硬编码的，我每次都必须手动更改每个范围引用。

Sub updateDataValidationRecords()

Range("A7").Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
Formula1:="=Status"

End Sub

问题：

有没有办法让上面的代码有一个变体，我可以提供一个单元格地址，并根据该单元格地址，为同一行中需要数据验证列表的其他单元格添加相应的数据验证列表？

提前感谢您的帮助，不胜感激。

Excel VBA 对象范围

Sub updateDataValidationRecordsBasedOnCell(cellAddress As String)
    Dim ws As Worksheet
    Dim validationRange As Range
    Dim rowDataRange As Range
    
    ' Set the worksheet where you want to apply the data validation
    Set ws = ThisWorkbook.Sheets("YourSheetName") ' Replace "YourSheetName" with the actual sheet name
    
    ' Set the validation range based on the provided cell address
    Set validationRange = ws.Range(cellAddress)
    
    ' Clear existing data validation in the specified range
    validationRange.Validation.Delete
    
    ' Determine the entire row for data validation starting from column A
    Set rowDataRange = ws.Rows(validationRange.Row)
    
    ' Add data validation to the entire row based on the named range "Status"
    With rowDataRange.Validation
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Formula1:="=Status"
        .IgnoreBlank = True
        .InCellDropdown = True
        ' You can customize other validation properties here if needed
    End With
End Sub



Sub TestUpdateDataValidation()
    updateDataValidationRecordsBasedOnCell "A7"
    ' Change the cell address as needed
End Sub

感谢您的输入，我是Excel VBA的新手。您提供的代码按设计工作，并将“状态”数据验证（DV）列表放在第 7 行的每个单元格中。对于我的方案，我在每行的特定单元格中有几个不同的 DV 列表。“状态”DV 列表是唯一与条件格式绑定的 DV 列表。因此，如果当前单元格是“A7”，那么我想将“状态”DV放在单元格“G7”中，“除法”DV列表放在单元格“B7”中，“模式”DV列表放在单元格“Q7”中，等等。

0赞 Aaron 11/12/2023

最初，当我第一次遇到这个问题时，我在想，由于 DV 列表位于整个电子表格中的特定列中，我将能够提供一个单元格添加值，并且代码将能够获取该值并相对于该单元格地址偏移每个 DV 列表的位置。因此，如果我在“子”TestUpdateDataValidation“中提供”A7“作为 rthe 值，那么它将在 With 中的任何 DV 列表名称中放置一个特定的 DV 列表。添加 Type：=xlValidateList“ 参数。

0赞 Aaron 11/12/2023

问：是否可以让“updateDataValidationRecordsBasedOnCell ”A7“”引用当前单元格地址，或者让输入框将单元格地址传输到“updateDataValidationRecordsBasedOnCell”函数？再次感谢您为我提供此问题的帮助，如果我在原来的问题中不清楚，我深表歉意。

0赞 Aaron 11/13/2023

我还没有机会尝试您的解决方案。谢谢。我确实找到了一个解决方案，就像您更优雅的解决方案一样，使用 InputBox 函数来识别具有数据验证更新的单元格。你能给我一些关于如何满足你的要求的指导吗？如何分配总分？

0赞 Mark 11/14/2023

只需使用每个答案左上角的箭头之一，即可对相应的答案投赞成票（如果有帮助）或反对票。

2赞 Mark 11/12/2023 #2

不确定我是否理解你的问题正确，但这可能如您描述的那样有效。

Sub updateDataValidationRecordsBasedOnCell(Optional cellAddress As String)
Dim ws As Worksheet
Dim validationRange As Range
Dim rowDataRange As Range

' Set the worksheet where you want to apply the data validation
Set ws = ThisWorkbook.Sheets("YourSheetName") ' Replace "YourSheetName" with the actual sheet name

' If cellAddress is not provided, ask the user for input
If cellAddress = "" Then
    On Error Resume Next
    cellAddress = InputBox("Enter cell address:", "Cell Address", ActiveCell.Address)
    On Error GoTo 0
End If

' Exit the sub if the user cancels the InputBox
If cellAddress = "" Then Exit Sub

' Set the validation range based on the provided cell address
Set validationRange = ws.Range(cellAddress)

' Clear existing data validation in the specified range
validationRange.Validation.Delete

' Determine the entire row for data validation starting from column A
Set rowDataRange = ws.Rows(validationRange.Row)

' Add data validation to the entire row based on the named range "Status"
With rowDataRange.Validation
    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Formula1:="=Status"
    .IgnoreBlank = True
    .InCellDropdown = True
    ' You can customize other validation properties here if needed
End With

结束副

上一个：VBA Web Scraper For 循环从 Excel 列 C 输入数据，给出运行时错误“438”：对象不支持此属性或方法

下一个：如何定义某个范围内的地址/单元格位置

通过 VBA 更新损坏的数据验证列表

Updating Broken Data Validation Lists via VBA

评论

评论