Excel VBA Protect Sheet without Locking all cells

0 votes

In an Excel Spreadsheet, I'm attempting to safeguard the headers. I did this by selecting the full sheet, going to the cell properties page, and unchecking the "locked" box. I then checked "locked" and only chose the first row.

My macros work well the first time, but when I try to run them again, I get errors about the sheets being locked, and when I go back and check my sheets, EVERY cell is now locked once more. There is no VBA code in my programme that directs me to lock any cells. To safeguard the sheets, I'm running the following macro:

Public Sub ProtectSheet(Optional sheetname As String)
    
    Dim thisSheet As Worksheet
    'This is to protect sheet from userinterface, but not from macros
    If IsMissing(sheetname) Then sheetname = ""
    If sheetname = "" Then
        Set thisSheet = ActiveWorkbook.ActiveSheet
    Else
        Set thisSheet = ActiveWorkbook.Worksheets(sheetname)
    End If
    
    thisSheet.Protect UserInterfaceOnly:=False, Contents:=True, DrawingObjects:=True, Scenarios:=True, AllowFormattingRows:=True, AllowFormattingColumns:=True, AllowFormattingCells:=True, AllowInsertingHyperlinks:=True, AllowSorting:=True, AllowFiltering:=True

End Sub

I created the VBA code as follows to unprotect the sheet, select all and unlock, then lock the first row, then protect. It works when I do this, but I don't understand why I have to.

Public Sub ProtectSheet(Optional sheetname As String)
    
    Dim thisSheet As Worksheet
    'This is to protect sheet from userinterface, but not from macros
    If IsMissing(sheetname) Then sheetname = ""
    If sheetname = "" Then
        Set thisSheet = ActiveWorkbook.ActiveSheet
    Else
        Set thisSheet = ActiveWorkbook.Worksheets(sheetname)
    End If
    
    thisSheet.Unprotect
    thisSheet.Cells.Locked = False
    thisSheet.Rows(1).Locked = True
    thisSheet.Protect UserInterfaceOnly:=False, Contents:=True, DrawingObjects:=True, Scenarios:=True, AllowFormattingRows:=True, AllowFormattingColumns:=True, AllowFormattingCells:=True, AllowInsertingHyperlinks:=True, AllowSorting:=True, AllowFiltering:=True

End Sub

I would prefer not to have to enter this additional code when I don't think I should have to because I want to understand WHY all of my cells are locking. Is there a flaw in Excel that makes it set the locked attribute, or am I overlooking something in this code that locks them by default?

Dec 28, 2022 in Others by Kithuzzz
• 28,900 points
61 views

1 answer to this question.

0 votes

If the problem is the use of Clear then consider creating a separate sub to manage that, and call it instead of Clear.

Sub ClearButUnlocked(rng As Range)
    with rng
        .clear
        .cells.locked=false
    end with
end sub
answered Dec 29, 2022 by narikkadan
• 53,520 points

Related Questions In Others

0 votes
1 answer

Converting all tabs of excel sheet to PDF

Using VBA, try it like this, for ...READ MORE

answered Sep 26, 2022 in Others by narikkadan
• 53,520 points
460 views
0 votes
1 answer

Protect excel sheet but allow data entry with form

Try this: Private Sub Workbook_Open() ...READ MORE

answered Sep 30, 2022 in Others by narikkadan
• 53,520 points
263 views
0 votes
1 answer

Is there a way to lock cells after editing an excel sheet that's on sharepoint?

The Excel Web version (which, based on ...READ MORE

answered Oct 3, 2022 in Others by narikkadan
• 53,520 points
174 views
0 votes
1 answer

Remove formulas from all worksheets in Excel using VBA

Try this : Option Explicit Sub test1() ...READ MORE

answered Oct 3, 2022 in Others by narikkadan
• 53,520 points
295 views
0 votes
1 answer

Retrieve epay.info Balance with VBA and Excel

This code should log you in, provided ...READ MORE

answered Sep 5, 2018 in Blockchain by digger
• 26,740 points
533 views
0 votes
1 answer

How to load file to Excel Power query from SFTP site

Currently, I don't think there is a ...READ MORE

answered Dec 3, 2018 in Power BI by Upasana
• 8,620 points
2,415 views
0 votes
1 answer

Using VBA Excel to create a gramatically correct list

The Excel AND function is a logical ...READ MORE

answered Feb 9, 2022 in Others by gaurav
• 22,960 points
117 views
0 votes
2 answers

How to copy a formula horizontally within a table using Excel VBA?

Hi so basically, create an adjacent column ...READ MORE

answered Feb 16, 2022 in Others by Edureka
• 13,630 points
300 views
0 votes
1 answer
0 votes
1 answer

How to print an Excel Sheet using VBA with Nitro PDF Creator in Excel 2016

you can use the built-in excel facilities ...READ MORE

answered Sep 24, 2022 in Others by narikkadan
• 53,520 points
242 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP