excel VBA代码出现问题-取消保护包含隐藏工作表的工作簿

z9smfwbn 于 2023-08-08 发布在其他

关注(0)|答案(1)|浏览(163)

我有一个问题，我的VBA代码，它不是解除保护我的工作簿与一些工作表是隐藏的。一个假设，我认为，是取消隐藏所有工作表，取消保护工作簿，并在此之后取消隐藏所有工作表，最初隐藏。
我已经试着建立了一个代码，但隐藏所有工作表的步骤，最初隐藏它的不工作！
拜托你们能不能帮我解释一下为什么它不起作用！？

Sub UnlockSheets()
' Define the password to unlock the tabs
Dim password As String
password = InputBox("Enter the password")
   
' Collection to store the original visibility state of the tabs
Dim originalState As New Collection

' Reference to the currently active worksheet
Dim activeSheet As Worksheet
Set activeSheet = ActiveSheet

' Loop through all the tabs in the workbook
For Each ws In ThisWorkbook.Worksheets
    ' Check if the tab is protected with a password
    If ws.ProtectContents Then
        ' Store the original visibility state of the tab
        originalState.Add ws.Visible, ws.Name
        ' Show the tab before unprotecting it (only if it was hidden)
        If ws.Visible = xlSheetHidden Or ws.Visible = xlSheetVeryHidden Then
            ws.Visible = xlSheetVisible
        End If
        
        ' Activate the tab before unprotecting it
        ws.Activate
        
        ' Unprotect the tab using the provided password
        On Error Resume Next
        ws.Unprotect Password:=password
        On Error GoTo 0
        ActiveWorkbook.Unprotect Password:=password
    End If
Next ws

' Return to the originally active worksheet
activeSheet.Activate

' Hide the tabs again (if they were originally hidden)
Dim tab As Variant
For Each tab In originalState
    On Error Resume Next
    If originalState(tab) = xlSheetHidden Or originalState(tab) = xlSheetVeryHidden Then
        Dim newTabName As String
        newTabName = Replace(tab, oldTabName, newTabName)
        ThisWorkbook.Worksheets(newTabName).Visible = originalState(tab)
    End If
    On Error GoTo 0
Next tab
End sub

字符串
我还有另一个疑问，我有这个相同的代码appllied一个按钮。当我从按钮上播放宏时，它在wb行上返回错误1004。当我从代码本身播放时，它工作了！这有什么逻辑可言！？

excel

来源：https://stackoverflow.com/questions/76733968/problem-with-the-vba-code-unprotect-workbook-with-hidden-sheets

1条答案

按热度按时间

wdebmtf21#

取消保护工作簿和工作表

在我这边（Windows 10 64比特，M365），您似乎可以取消保护工作表，而不需要取消保护活页簿。
能见度不重要。当活页簿受保护时，您无法变更它。也许是这样的情况下，你得到了一个错误！？
对于不同的保护“类型”，该行为可能不同。
尝试使用下面的输出注解和取消注解用*****指定的行，并隐藏工作表，以证明我错了。

Sub UnlockSheets()
    
    Dim Password As String: Password = InputBox("Enter the password")
       
    Dim wb As Workbook: Set wb = ThisWorkbook
    
    If wb.ProtectStructure Then
        Debug.Print "The workbook was protected."
        wb.Unprotect Password:=Password ' *****
    Else
        Debug.Print "The workbook was not protected."
    End If
    
    Dim ws As Worksheet
    
    For Each ws In wb.Worksheets
        If ws.ProtectContents Then
            Debug.Print "Worksheet """ & ws.Name & """ was protected."
            ws.Unprotect Password:=Password ' *****
        Else
            Debug.Print "Worksheet """ & ws.Name & """ was not protected."
        End If
    Next ws
    
    wb.Protect Password, True, False ' *****
    
End Sub

字符串

赞(0）回复(0）举报 2023-08-08

我来回答

excel VBA代码出现问题-取消保护包含隐藏工作表的工作簿

1条答案

取消保护工作簿和工作表

相关问题

热门标签

最新问答