How to enable scrolling on a protected sheet using VBA?

In Excel, if you have a protected sheet and you want to enable scrolling while keeping the sheet protected, you can use VBA to adjust the properties of the worksheet. Here’s how you can do it:

    Sub EnableScrollingOnProtectedSheet()
        Dim ws As Worksheet
        ' Set the worksheet you want to modify
        Set ws = ThisWorkbook.Sheets("YourSheetName")
        
        ' Unprotect the sheet
        ws.Unprotect Password:="YourPassword"
        
        ' Enable scrolling
        With ws
            .EnableSelection = xlNoRestrictions
        End With
        
        ' Protect the sheet again with the desired options
        ws.Protect Password:="YourPassword", UserInterfaceOnly:=True
    End Sub

Open the VBA Editor: Press `ALT` + `F11` to open the VBA Editor in Excel.
Insert a Module: In the VBA Editor, right-click on any of the items in the “VBAProject” list for your workbook, choose `Insert` -> `Module`. This will create a new module.
Add the VBA Code: Copy and paste the following code into the module window:

Replace `”YourSheetName”` with the name of the worksheet you want to enable scrolling on.
Replace `”YourPassword”` with the password you use to protect the sheet (if any).

Run the Macro: After pasting the code, you can run it by pressing `F5` while the cursor is within the code. Alternatively, you can close the VBA Editor and run the macro from Excel by going to `View` → `Macros` → `View Macros`, select `EnableScrollingOnProtectedSheet`, and click `Run`.
Save Your Workbook: Make sure to save your workbook in a macro-enabled format, such as `.xlsm`.

This code unprotects the sheet, sets the property to allow cell selection across the sheet, and protects it again with `UserInterfaceOnly:=True`. This allows macros to modify the sheet while keeping it protected from user edits.

Important Notes:

UserInterfaceOnly: This option allows VBA code to make changes to the sheet, even if it is protected. It must be set each time the workbook is opened, as this setting does not persist between sessions.
Password: If your sheet is protected with a password, you need to provide it in the code. If there isn’t a password, you can omit the `Password` parameters.

facebooklinkedin