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]

I'm just leaving for work so I'll look at your file once I get back if you have uploaded one where the VBA Project is not protected

 

[RAJESH1960]

I'm just leaving for work so I'll look at your file once I get back if you have uploaded one where the VBA Project is not protected

The unprotect password of the VBA Project is also 123. I too have to leave now. We will discuss this tonight and try to solve it.

 

[MARK858]

Just had a look at this at work....
I get an error if I run your ClearContents code from scratch as Sheets("Bank") and Sheets("A") are protected.
The cells on Sheet("Original") don't get locked or Protected for me if I comment out your code above the protection part of the code (I did move the password to be with the rest of the protect code).
If you mean the error you get when you run the code GenerateXML from the button, it is because Sheets("Bank"), Sheets("A") and Sheets("B") are protected when you do the pastespecial.
Your code is slow because you use Select so often.

 

[RAJESH1960]

Just had a look at this at work....
I get an error if I run your ClearContents code from scratch as Sheets("Bank") and Sheets("A") are protected.
The cells on Sheet("Original") don't get locked or Protected for me if I comment out your code above the protection part of the code (I did move the password to be with the rest of the protect code).
If you mean the error you get when you run the code GenerateXML from the button, it is because Sheets("Bank"), Sheets("A") and Sheets("B") are protected when you do the pastespecial.
Your code is slow because you use Select so often.

Did you try the link in post #8.? That is the working one. I don't get any error for the first xml, but when I try to use the same sheet for the next data the original sheet is protected automatically - even the unprotected cells where I need to paste the data.

 

[RAJESH1960]

?

 

[MARK858]

What should I do to stop the hidden sheets from protection

Remove

ws.Protect Password:=123

Edit: and yes I was using the file from post 8 and I have downloaded it again with exactly the same results

 

[RAJESH1960]

Remove

ws.Protect Password:=123

There is no such line in the code in post #8

 

[MARK858]

There is no such line in the code in post #8

It is in your HideSheets macro

 

[RAJESH1960]

Option Explicit

Sub HideSheets()
    Dim ws As Worksheet
    ThisWorkbook.Unprotect Password:=123
    For Each ws In ThisWorkbook.Worksheets
        If ws.Name <> "Original" Then
        ws.Visible = xlSheetVeryHidden
        End If
    Next ws
    
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

It is unprotect....in both

 

[MARK858]

The code I see is

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