Unprotect selected columns permanently

RAJESH1960

Well-known Member
Joined
Mar 26, 2020
Messages
2,173
Office Version
  1. 2019
Platform
  1. Windows
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.

VBA Code:
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
 

RAJESH1960

Well-known Member
Joined
Mar 26, 2020
Messages
2,173
Office Version
  1. 2019
Platform
  1. Windows
The cells in Workbook "Right codes" Sheets("Original") are not unlocked in columns A:I in the file that you uploaded before I run anything, they are LOCKED. It is only Column J that has cells unlocked.

Columns("A:I") are locked at the start and so they will be locked at the finish.
I did notice that now. Maybe the first time I pasted the data and then protected the sheet. You are right. Now for the solution... Back to the drawing board....?
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

RAJESH1960

Well-known Member
Joined
Mar 26, 2020
Messages
2,173
Office Version
  1. 2019
Platform
  1. Windows
Thanks Mark. You really helped me find the problem. Till I find a solution, for now, I have just added one line in the ClearData code to leave the original sheet unprotected.
VBA Code:
HideSheets
ActiveSheet.Unprotect Password:=123

End Sub
Thanks for your patience and time Mark.
 

Forum statistics

Threads
1,175,670
Messages
5,898,803
Members
434,731
Latest member
njakfla

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top