我有以下的vba代码,它能够正常工作;然而,当我锁定/保护我的工作表时它无法正常工作,即使我在单元格格式选项中取消了相关单元格的“锁定”选项。
为什么当工作表被锁定时会出现
错误1004应用程序或对象定义错误
?
Private Sub Worksheet_Change(ByVal Target As Range)
Application.ScreenUpdating = False
Application.DisplayAlerts = False
If Range("B10").Value <> "" Then
Dim MyList(9) As String
MyList(0) = "60 Days EOM"
MyList(1) = "60 Days DOI"
MyList(2) = "45 Days EOM"
MyList(3) = "45 Days DOI"
MyList(4) = "30 Days EOM"
MyList(5) = "30 Days DOI"
MyList(6) = "14 Days DOI"
MyList(7) = "10 Days EOM"
MyList(8) = "7 Days DOI"
MyList(9) = "Immediate Payment"
With Range("N38").Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, Formula1:=Join(MyList, ",")
End With
If Intersect(Target, Target.Worksheet.Range("N38")) Is Nothing Then
Range("N38").Value = "60 Days EOM"
End If
Else
Range("N38").Validation.Delete
Range("N38").Value = ""
End If
End Sub