时间戳

首页 > 解决方案 > 保护时宏抛出错误

vba - 保护时宏抛出错误

问题描述

我试图通过调用另一个宏来解锁工作表以允许 RFID 阅读器扫描和输入数据，然后再次保护文档以使其不被编辑，从而使宏运行。

Private Sub Worksheet_Change(ByVal Target As Range)
' call another macro
 Call UnProtect
' End Sub
' Dim i As Integer
' MsgBox (Target.Row & ":" & Target.Column)

' For i = 8 To 200
If Target.Column = 3 And Target.Row <= 12 Then
  If Cells(Target.Row, Target.Column).Value <> "" And Cells(Target.Row, "E").Value = "" Then

    ' Cells(Target.Row, "E").Value = Now()
    ' Cells(Target.Row, "E").NumberFormat = "h:mm AM/PM"
  End If
End If
' Next
' Range("E:E").EntireColumn.AutoFit
' End Sub

' Private Sub Worksheet_Change(ByVal Target As Range)
' Dim i As Integer
' MsgBox (Target.Row & ":" & Target.Column)

' For i = 8 To 200
If Target.Column = 1 And Target.Row <= 17 Then
  If Cells(Target.Row, Target.Column).Value <> "" And Cells(Target.Row, "B").Value = "" Then

    Cells(Target.Row, "B").Value = Now()
    ' Cells(Target.Row, "F").NumberFormat = "h:mm AM/PM"
  End If
End If
' Next
' Range("F:F").EntireColumn.AutoFit
' End Sub

' Private Sub Worksheet_Change(ByVal Target As Range)
' Dim i As Integer
' MsgBox (Target.Row & ":" & Target.Column)

' For i = 8 To 200
If Target.Column = 3 And Target.Row >= 15 Then
  If Cells(Target.Row, Target.Column).Value <> "" And Cells(Target.Row, "D").Value = "" Then
        Cells(Target.Row, "D").Value = Now()
        Cells(Target.Row, "D").NumberFormat = "mm/dd/yyy"
  End If
End If
' Next
Range("D:D").EntireColumn.AutoFit
' End Sub
' call another macro
    Call Protect
End Sub

它调用的两个宏是

Sub UnProtect()
'Unprotect a worksheet
Sheets("Sign in Sheet").UnProtect
End Sub

和

Sub Protect()
'Protect a worksheet
Sheets("Sign in Sheet").Protect
End Sub

但是，当我只包含解锁宏时，代码可以正常工作。但是当我添加保护宏时，会出现错误代码

运行时错误“1004”：无法设置 Range 类的 NumberFormat 属性

它指向

Cells(Target.Row, "D").NumberFormat  = "mm/dd/yyyy"

关于到底发生了什么的任何想法。

标签： vbaexcel

解决方案

当 Target 不仅仅是一个单元格并且 Worksheet_Change 很有可能试图在其自身之上运行时，我没有看到任何规定。

循环浏览 Target 中的每个范围对象并禁用事件触发器。

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

    On Error GoTo safe_exit
    Application.EnableEvents = False
    Call Unprotect
    Dim t As Range

    If Not Intersect(Target, Range("A1:A17")) Is Nothing Then
        For Each t In Intersect(Target, Range("A1:A17"))
            If t.Value <> vbNullString And t.Offset(0, 1).Value <> vbNullString Then
                t.Offset(0, 1) = Now
                t.Offset(0, 1).NumberFormat = "h:mm AM/PM"
            End If
        Next t
    End If

    If Not Intersect(Target, Range("C1:C12")) Is Nothing Then
        For Each t In Intersect(Target, Range("C1:C12"))
            If t.Value <> vbNullString And t.Offset(0, 2).Value <> vbNullString Then
                t.Offset(0, 2) = Now
                t.Offset(0, 2).NumberFormat = "h:mm AM/PM"
            End If
        Next t
        'I don't understand why column F comes into play here
        ' Range("F:F").EntireColumn.AutoFit
    End If

    If Not Intersect(Target, Range("C15:C999999")) Is Nothing Then
        For Each t In Intersect(Target, Range("C15:C999999"))
            If t.Value <> vbNullString And t.Offset(0, 1).Value <> vbNullString Then
                t.Offset(0, 1) = Now
                t.Offset(0, 1).NumberFormat = "mm/dd/yyy"
            End If
        Next t
        Range("D:D").EntireColumn.AutoFit
    End If

    Call Protect
safe_exit:
    Application.EnableEvents = True
End Sub

推荐阅读