选择行或列时 Excel 日期选择器子崩溃

Question

我在 Excel 表中添加了一个日期选择器，下面有 Private Sub。当我选择一行或一列时，我得到一个运行时错误 1004。当我选择调试时，下面的行被突出显示为错误:
.Left = Target.Offset(0, 1).Left

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
 
 With Sheet1.DTPicker1
    .Height = 20
    .Width = 20
    If Not Intersect(Target, Range("A7:B135,J7:J135")) Is Nothing Then
      .Visible = True
      .Top = Target.Top
      .Left = Target.Offset(0, 1).Left
      .LinkedCell = Target.Address
    Else
      .Visible = False
    End If
  End With
End Sub

非常感谢您的帮助
缺口

Answer 1

最好使用Intersect创建一个新范围并使用它来代替 Target像这样的东西:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
   Dim rng As Range
   
   With Sheet1.DTPicker1
       .Height = 20
       .Width = 20
       
       Set rng = Intersect(Target, Me.Range("A7:B135,J7:J135"))
       if rng Is Nothing then
           .Visible = False
       Else
           if rng.cells.count > 1 Then
               .Visible = False
           else 
               .Visible = True
               .Top = rng.Top
               .Left = rng.Offset(0, 1).Left
               .LinkedCell = rng.Address
           End if
        End If
    End With
End Sub