Skip to content
TheChatArchiveThe Chat Archive
gpt-3.5-turbo-0613· openaiHugging Face Suzhen/CodeChat

How can I lock certain sheets in a workbook when the workbook opens

  1. User

    How can I lock certain sheets in a workbook when the workbook opens

  2. Assistant

    To lock certain sheets in a workbook when the workbook opens, you can follow these steps:

    1. Open Excel and the workbook that you want to lock certain sheets in.
    2. Press Alt + F11 to open the Visual Basic for Applications (VBA) editor.
    3. In the VBA editor, find the Project Explorer window on the left-hand side (if it's not visible, you can enable it by pressing Ctrl + R).
    4. Expand the workbook by clicking on the "+" icon next to its name.
    5. Double-click on the "ThisWorkbook" module, which is located under the workbook's name.
    6. In the code window that opens, paste the following code:
    Private Sub Workbook_Open()
        Dim ws As Worksheet
        For Each ws In ThisWorkbook.Worksheets
            If ws.Name = "Sheet1" Or ws.Name = "Sheet2" Then
                ws.Protect Password:="YourPassword", userinterfaceonly:=True
            End If
        Next ws
    End Sub
    
    1. Replace "Sheet1" and "Sheet2" with the names of the sheets you want to lock.
    2. Modify "YourPassword" with the desired password to lock and unlock the sheets.
    3. Save the changes and close the VBA editor.
    4. Close and reopen the workbook to trigger the Workbook_Open event.
    5. The sheets specified in the code will now be locked, and you will need to enter the password to make changes to them.

    Note: The userinterfaceonly:=True argument allows users to interact with the locked sheets if they already have the password. It provides better user experience by only protecting the sheets programmatically, but still allowing access through the Excel interface. Also, remember to keep a backup of your workbook before making changes to the code.

Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.