不知道我是否理解了你的问题，但这可能会像你描述的那样工作。

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

字符串
End Sub

可能对你的案子有用

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

字符串