将数据添加到受密码保护的工作表

I have a sheet with password. But I need to add some data from vba code. How can I do this? Maybe I can reset password, then add data and then set it again

我有一张密码表。但我需要从vba代码中添加一些数据。我怎样才能做到这一点?也许我可以重置密码,然后添加数据然后再次设置

 Me.Parent.Worksheets("Sheet1").Unprotect 111

Me.Parent.Worksheets("Sheet1").Protect Password:=111, DrawingObjects:=True, Contents:=True, Scenarios:=True

When I use simple password, code works, but if I set pass: pass111111 - there is an error Wrong password

当我使用简单的密码,代码工作,但如果我设置传递:pass111111 - 错误密码错误

3 个解决方案

#1

It's easy:

sub Protect_Sheet()
Thisworkbook.Sheets(1).Unprotect Password:="Password"

'Do something 

Thisworkbook.Sheets(1).Protect Password:="Password"

End Sub

#2

I would suggest this code to protect your worksheet

我建议使用此代码来保护您的工作表

Sub ProtectSheet()
    Sheets("Sheet1").Protect Password:="111111", _
    Contents:=True, userInterfaceOnly:=True
End Sub

Note the use of userInterfaceOnly:=True

注意使用userInterfaceOnly:= True

When you use the Protect method with UserInterfaceOnly argument set to True as shown above then you protect the user interface but not macros. So you don't need to UnProtect the sheet to add data from macros. You can directly perform actions with it. For example

当您使用Protect方法并将UserInterfaceOnly参数设置为True时,如上所示,您可以保护用户界面,但不保护宏。因此,您无需取消保护工作表以从宏添加数据。您可以直接使用它执行操作。例如

Sub ProtectSheet()
    Sheets("Sheet1").Protect Password:="111111", _
    Contents:=True, userInterfaceOnly:=True

    Sheets("Sheet1").Range("A1").Value = "Hello World!"
End Sub

#3

If you have already set it to be protected with Password = 111 You will have to unprotect with that password, you can then reprotect with a new password:

如果您已将其设置为使用Password = 111保护,则必须取消保护该密码,然后可以使用新密码重新保护:

        Me.Parent.Worksheets("Sheet1").Unprotect Password:="111"

        Me.Parent.Worksheets("Sheet1").Protect Password:="pass111111", DrawingObjects:=True, Contents:=True, Scenarios:=True

That's the only reason I can think of to explain this.

这是我能想到解释这一点的唯一原因。

#1