Unprotect selected columns permanently

[RAJESH1960]

Hello guys, I am able to hide and unhide the sheets when I run the code. The sheet (Original) I am supposed to enter a new data every time, to generate XML is protected and only the columns (A:J) where I am supposed to paste the data are unlocked. I have already unlocked the cells of those rows and protected the rest of the columns with a password. I have no idea why are the unprotected cells getting locked when I run the macro. I need your expert help to find the solution to solve this problem.

Option Explicit
Sub HideSheets()
    Dim ws As Worksheet
    ThisWorkbook.Unprotect Password:=123
    For Each ws In ThisWorkbook.Worksheets
        If ws.Name <> "Original" Then
            ws.Protect Password:=123
            ws.Visible = xlSheetVeryHidden
        End If
    Next ws
    ThisWorkbook.Protect Password:=123
End Sub


Sub UnHideSheets()
    Dim ws As Worksheet
    ThisWorkbook.Unprotect Password:=123
    For Each ws In ThisWorkbook.Worksheets
        If ws.Name <> "Original" Then
            ws.Visible = xlSheetVisible
            
        End If
    Next ws
    
End Sub

 

[MARK858]

When you protect the sheets you probably have the Select unlocked cells option unchecked

 

[RAJESH1960]

When you protect the sheets you probably have the Select unlocked cells option unchecked View attachment 53920

Mark. I have checked both the select locked cells and select unlocked cells.

 

[RAJESH1960]

I hope the placement of hidesheets and protect sheet are in the right line in the macro.

ActiveSheet.Unprotect Password:=123
    ActiveSheet.Shapes.Range(Array("Button 5")).Select
    ActiveSheet.Shapes("Button 5").ZOrder msoSendBackward
    Range("K9").Select
    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
HideSheets
ActiveSheet.Protect Password:=123

End Sub

 

[MARK858]

The password would normally be on the same line as the rest of the protect code

    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, Password:="123"
    ActiveSheet.EnableSelection = xlNoRestrictions
 HideSheets

Although I have no idea what sheet is active at the time the code is run

 

[RAJESH1960]

The password would normally be on the same line as the rest of the protect code

    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, Password:="123"
    ActiveSheet.EnableSelection = xlNoRestrictions
 HideSheets

Although I have no idea what sheet is active at the time the code is run

All the other 9 sheets including Sheet "Original" is active when I run the code. As I said, the hide and unhide part is working correctly. But, the problem is, after I run the second code, in the end the sheet Original which has unprotected cells get protected. Every time when I have to generate xml I have to unprotect the sheet to paste the data. I need help in editing the code for the cells to stay unprotected so that I can paste data in those cells without unprotecting it each time.

 

[RAJESH1960]

Mark for your reference I am sharing the link so that you can understand better. FYI I have been using this on different data till now successfully. When the user works on this workbook, I want only the sheet Original to display. The rest of the work is done by the code.

Test Protection.xlsm

drive.google.com

 

[RAJESH1960]

Sorry, sent the wrong non working link. This is the right link. The code is slow. It takes 2 minutes to run each macro. When I try to paste the second data to generate xml. it displays that the sheet is protected and hence I can't paste the data without protecting it.

Test Protection.xlsm

drive.google.com

 

[MARK858]

All the other 9 sheets including Sheet "Original" is active

You can only have one ActiveSheet

 

[RAJESH1960]

Original Sheet is the only sheet active. But when I run the macro, the code activates all the other sheets for the macro to run through and get the result.