保护工作簿中的所有工作表

I want to protect all the sheets in a workbook based on username, so workbooks will be protected from external use, whereas internally we can easily use. However, the code just always states that the line If ws.Protect = True Then is false, even when I know the sheet is protected...

我想基于用户名保护工作簿中的所有工作表,因此工作簿将不受外部使用的影响,而在内部我们可以轻松使用。但是,代码只是总是声明行如果ws.Protect = True则为false,即使我知道工作表受到保护...

Private Sub Workbook_Open()
Dim strUser, Num, myCount, ws

    strUser = CreateObject("WScript.Network").UserName
    strUser = LCase(strUser)
    Num = CLng(Right(strUser, 6))


    If Left(strUser, 1) = "D" And Len(strUser) = 11 And IsNumeric(Num) Then
    For Each ws In ActiveWorkbook.Worksheets
    If ws.Protect = True Then
        ws.Unprotect "password"
    Else
        ws.Protect "password", DrawingObjects:=True, Contents:=True, _
                        AllowSorting:=True, AllowFiltering:=True
    End If
    Next ws
    End If

End Sub

any help would be appreciated!

任何帮助,将不胜感激!

3 个解决方案

#1

You need to look at the ProtectContents property, so

你需要查看ProtectContents属性,所以

If ws.Protect = True Then should be If ws.ProtectContents = True Then

如果ws.Protect = True那么应该是如果ws.ProtectContents = True那么

#2

ws.protect is the command to protect the sheet not check if its protected. You can use

ws.protect是保护工作表的命令,不检查它是否受保护。您可以使用

ActiveSheet.ProtectContents

ActiveSheet.ProtectDrawingObjects

activeSheet.ProtectScenarios

in your if statements to check if the sheet is protected in any combination of these properties.

在if语句中检查工作表是否受这些属性的任意组合的保护。

Look here for more info: https://support.microsoft.com/en-us/kb/161245

在这里查看更多信息:https://support.microsoft.com/en-us/kb/161245

#3

I just want to comment that your user name checking code seems like it will fail a lot:

我只想评论你的用户名检查代码似乎会失败很多:

Num = CLng(Right(strUser, 6)) this will give error if strUser does not end with 6 digits.
What you want is IsNumeric( Right(strUser, 6) )
Actually, dont even use IsNumeric because for example IsNumeric("1,234.56") will return True.

Num = CLng(右(strUser,6))如果strUser没有以6位数结尾,这将给出错误。你想要的是IsNumeric(右(strUser,6))实际上,甚至不使用IsNumeric,因为例如IsNumeric(“1,234.56”)将返回True。

Left(strUser, 1) = "D" this will be False because you convert the strUset to lower case with strUser = LCase(strUser) (unless you have Option Compare Text in the begining of the file)

左(strUser,1)=“D”这将是False,因为你使用strUser = LCase(strUser)将strUset转换为小写(除非你在文件的开头有选项比较文本)

You can shorten the check to

您可以将支票缩短至

Private Sub Workbook_Open()
    Dim strUser$, ws As Worksheet
    strUser = Environ$("UserName")
    If Not strUser Like "[Dd]????######" Then Exit Sub  ' ? matches any character, # matches any digit from 0 to 9, and [Dd] matches upper or lower case D

    For Each ws In ActiveWorkbook.Worksheets
        If ws.ProtectContents = True Then
            ws.Unprotect "password"
        Else
            ws.Protect "password", DrawingObjects:=True, Contents:=True, _
                     AllowSorting:=True, AllowFiltering:=True
        End If
    Next ws
End Sub

#1