excel - 将 ListRow 添加到 protected 工作表的表中

Question

当工作表受到保护时，我想将数据添加到每个工作表中每个表的最后一行。

我在 ThisWorkbook 中有这段代码来保护工作表

Private Sub Workbook_Open()
    Dim wSheet As Worksheet
    For Each wSheet In Worksheets
        wSheet.Protect Password:="Secret", _
        UserInterFaceOnly:=True
    Next wSheet
End Sub

和下面的代码来添加数据。它抛出

Error 1004 "Application-defined or Object-defined error"

在 Set newrow1 = tbl.ListRows.Add 时工作表受到保护。

Sub AddDataToTable()
    Application.ScreenUpdating = False
    Dim MyValue As String
    Dim sh As Worksheet
    Dim ws1 As Worksheet
    Dim ws2 As Worksheet
    Dim ws3 As Worksheet
    Dim ws4 As Worksheet
    Dim ws5 As Worksheet
    Set ws1 = Sheets("Setting")
    Set ws2 = Sheets("R_Buy")
    Set ws3 = Sheets("R_Sell")
    Set ws4 = Sheets("S_Buy")
    Set ws5 = Sheets("S_Sell")
    Dim tbl As ListObject
    Dim tb2 As ListObject
    Dim tb3 As ListObject
    Dim tb4 As ListObject
    Dim tb5 As ListObject
    Set tbl = ws1.ListObjects("T_Setting")
    Set tb2 = ws2.ListObjects("T_R_Buy")
    Set tb3 = ws3.ListObjects("T_R_Sell")
    Set tb4 = ws4.ListObjects("T_S_Buy")
    Set tb5 = ws5.ListObjects("T_S_Sell")
    Dim newrow1 As ListRow
    Dim newrow2 As ListRow
    Dim newrow3 As ListRow
    Dim newrow4 As ListRow
    Dim newrow5 As ListRow

    MyValue = InputBox("Add To Table, this cannot be undone")

    'check if user clicked Cancel button and, if appropriate, execute statements
    If StrPtr(MyValue) = 0 Then
        'display message box confirming that user clicked Cancel button
        MsgBox "You clicked the Cancel button"
        'check if user entered no input and, if appropriate, execute statements
    ElseIf MyValue = "" Then
        'display message box confirming that user entered no input
        MsgBox "There is no Text Input"
    Else
        Set newrow1 = tbl.ListRows.Add
        With newrow1
            .Range(1) = MyValue
        End With

        Set newrow2 = tb2.ListRows.Add
        With newrow2
            .Range(1) = MyValue
        End With

        Set newrow3 = tb3.ListRows.Add
        With newrow3
            .Range(1) = MyValue
        End With

        Set newrow4 = tb4.ListRows.Add
        With newrow4
            .Range(1) = MyValue
        End With

        Set newrow5 = tb5.ListRows.Add
        With newrow5
            .Range(1) = MyValue
        End With
    End If
    Application.ScreenUpdating = True
End Sub

Answer 1

这是 Excel 的一个问题，它不允许在 UserInterFaceOnly:=True 模式下编辑表格。不幸的是，我发现的唯一解决方法是在应用任何表方法之前取消保护，然后在以下之后重新保护:

.Unprotect Password:=SHEET_PW 'unprotect sheet          
'edit table         
.Protect Password:=SHEET_PW, UserInterFaceOnly:=True 'reprotect 

此外，我建议进行以下改进以缩短您的代码:

• 使用数组 Dim tbl(1 To 5) 而不是多个变量 tbl1, tbl2, tbl3, ...
• 或者最好使用数组仅列出您的工作表名称。
• 使用更具描述性的变量名(让您的生活更容易维护和阅读代码)
• 如果您的表格名称​​总是 T_ 后跟工作表名称，您可以轻松地从工作表名称中生成它们。
• 为您的工作表密码 SHEET_PW 使用常量，将其仅存储在一个地方(更容易更改，防止拼写错误)。
• 使用循环来做重复的事情。

所以我们最终得到:

Option Explicit

Const SHEET_PW As String = "Secret" 'global password for protecting worksheets

Public Sub AddDataToTableImproved()
    Dim AddValue As String
    AddValue = InputBox("Add To Table, this cannot be undone")

    If StrPtr(AddValue) = 0 Then 'cancel button
        MsgBox "You clicked the Cancel button"
        Exit Sub
    ElseIf AddValue = "" Then 'no input
        MsgBox "There is no Text Input"
        Exit Sub
    End If

    Dim NewRow As ListRow

    Dim SheetNameList() As Variant
    SheetNameList = Array("Setting", "R_Buy", "R_Sell", "S_Buy", "S_Sell")

    Dim SheetName As Variant
    For Each SheetName In SheetNameList
        With ThisWorkbook.Worksheets(SheetName)
            .Unprotect Password:=SHEET_PW 'unprotect sheet

            Set NewRow = .ListObjects("T_" & SheetName).ListRows.Add
            NewRow.Range(1) = AddValue

            .Protect Password:=SHEET_PW, UserInterFaceOnly:=True 'reprotect it
        End With
    Next SheetName
End Sub