通过列名确定目标单元格是否在表格列中

debugcn 发表于 Dev

贝尔扎布德

如果目标单元格在特定列中，我想运行一个过程。

我正在使用列号来确定这一点。如果将额外的列添加到表中，系统就会崩溃。

我的代码如下；

列名称是“活动”、“资源”和“利益相关者”（在表“时间表”中）；
列是 5、7 和 17，在行中 If Target.Column = 5 Or Target.Column = 7 Or Target.Column = 17 Then

If Target.Count > 1 Then GoTo exitHandler

On Error Resume Next
Set rngDV = Cells.SpecialCells(xlCellTypeAllValidation)
On Error GoTo exitHandler

If rngDV Is Nothing Then GoTo exitHandler

If Intersect(Target, rngDV) Is Nothing Then
   'do nothing
Else
    Application.EnableEvents = False
    newVal = Target.Value
    Application.Undo
    oldVal = Target.Value
    Target.Value = newVal
    If Target.Column = 5 Or Target.Column = 7 Or Target.Column = 17 Then
        If oldVal = "" Then
            'do nothing
        Else
            If newVal = "" Then
                'do nothing
            Else
                lUsed = InStr(1, oldVal, newVal)
                If lUsed > 0 Then
                    If Right(oldVal, Len(newVal)) = newVal Then
                        Target.Value = Left(oldVal, Len(oldVal) - Len(newVal) - 2)
                    Else
                        Target.Value = Replace(oldVal, newVal & ", ", "")
                    End If
                Else
                    Target.Value = oldVal _
                      & ", " & newVal
                End If
            End If
        End If
    End If
End If

exitHandler:
  Application.EnableEvents = True

SJR

因此，您可以使用表的属性。

Private Sub Worksheet_Change(ByVal Target As Range)

Dim c As Long

With ActiveSheet.ListObjects("Table1")
    c = Target.Column - .ListColumns(1).Range.Column + 1
    If Intersect(Target, .DataBodyRange) Is Nothing Then Exit Sub
    If .HeaderRowRange(c).Value = "Resources" Or _
       .HeaderRowRange(c).Value = "Activity" Or _
       .HeaderRowRange(c).Value = "Stakeholders" Then
            MsgBox "Yes"
    End If
End With

End Sub

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。