probelm when apply locked for cells in protection option and lock scroll

[mazan2010]

Hello Everyone
I have worksheet formula in the following cells B7,B9, D9, B11, D11 and range B15:B10014 and C15:C10014
the problem now when I make lock for these cells and protect sheet with password, the macro which populate data in A15:A10014 not working and give me the following error

also I prevent scroll from properties of sheet but when I close workbook and reopen again, its allow for scroll normal

how can i solve this issue

the macro code that populate data is

Sub RectangleRoundedCorners1_Click()
    
    Dim xWs As Worksheet
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    For Each xWs In Application.ActiveWorkbook.Worksheets
        If xWs.Name <> "MyList" Then
            xWs.Delete
        End If
    Next
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True

    Dim strPath As String, strFile As String, i As Long
    
    ' Prompt user to select a folder
    With Application.FileDialog(msoFileDialogFolderPicker)
        .InitialFileName = "C:\Temp\"               ' Default path
        .Title = "Please Select a Folder,{Coded by ExcelForum}"
        .ButtonName = "Select Folder"
        .AllowMultiSelect = False
        .Show
        If .SelectedItems.Count = 0 Then Exit Sub   ' User clicked cancel
        strPath = .SelectedItems.Item(1) & Application.PathSeparator
    End With
    
        Application.ScreenUpdating = False
    With Sheets("MyList")
        .Range("A15", .Range("A" & Rows.Count).End(xlUp).Offset(1)).ClearContents
        i = 14
        strFile = Dir$(strPath & "*.*")
        Do While Len(strFile) > 0
            i = i + 1
            .Hyperlinks.Add Anchor:=.Range("A" & i), _
                            Address:=strPath & strFile, _
                            TextToDisplay:=strFile
            strFile = Dir$
        Loop
        Columns.AutoFit
    End With
    Application.ScreenUpdating = True
    Worksheets("MyList").Range("A:D").ColumnWidth = 40              ' for adjust column width
    
End Sub

[mazan2010]

to share information with forum community
i find solution for these error
add the following line to my code that will unprotect worksheet first and at the end of code protect sheet again
ActiveSheet.Unprotect Password:="123456"
ActiveSheet.protect Password:="123456"
so my code will be like the following

Sub RectangleRoundedCorners1_Click()
    ActiveSheet.Unprotect Password:="123456"
    Dim xWs As Worksheet
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    For Each xWs In Application.ActiveWorkbook.Worksheets
        If xWs.Name <> "MyList" Then
            xWs.Delete
        End If
    Next
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True

    Dim strPath As String, strFile As String, i As Long
    
    ' Prompt user to select a folder
    With Application.FileDialog(msoFileDialogFolderPicker)
        .InitialFileName = "C:\Temp\"               ' Default path
        .Title = "Please Select a Folder,{Coded by ExcelForum}"
        .ButtonName = "Select Folder"
        .AllowMultiSelect = False
        .Show
        If .SelectedItems.Count = 0 Then Exit Sub   ' User clicked cancel
        strPath = .SelectedItems.Item(1) & Application.PathSeparator
    End With
    
        Application.ScreenUpdating = False
    With Sheets("MyList")
        .Range("A15", .Range("A" & Rows.Count).End(xlUp).Offset(1)).ClearContents
        i = 14
        strFile = Dir$(strPath & "*.*")
        Do While Len(strFile) > 0
            i = i + 1
            .Hyperlinks.Add Anchor:=.Range("A" & i), _
                            Address:=strPath & strFile, _
                            TextToDisplay:=strFile
            strFile = Dir$
        Loop
        Columns.AutoFit
    End With
    Application.ScreenUpdating = True
    Worksheets("MyList").Range("A:D").ColumnWidth = 40              ' for adjust column width
    ActiveSheet.protect Password:="123456"
End Sub

other thank scroll probelm , when find solution will share it here also

[robin8114]

Hi Mazan,

for worksheet protection you can add the following to the ThisWorkbook portion

Private Sub Workbook_Open()
    
    Sheet1.Protect "123456", userInterfaceOnly:=True

End Sub

This will eliminate the need to unprotect and protect every time your code runs.

As for the scroll problem, how are you preventing scrolling?

[mazan2010]

Thank you Robin8114 for your code
for scroll
I want to prevent scroll for sheet name (MyList)
only can scroll column A:E in range from A1:E10014
other than can't scroll

[robin8114]

So I did something like this:

in ThisWorbook module have this code

Private Sub Workbook_Open()
    Sheet1.ScrollArea = "A1:E100"
End Sub

that way every time workbook is opened the scrollarea is set

[mazan2010]

thank you so much robin8114
Also, I made another idea want to share with community here
I hide this range I didn't want from my sheet, shows only will be visible what I want