CODEWITHSUNDEEP
excelvba
Vanitas
Vanitas

Reputation: 23

Protect and unprotect a specified selection of cells

I think it's quite a simple task that only a rookie like me can't solve. This is the situation:

Right now I have some cells that the operator has to fill, he/she will activate the macro and a new formatted table will be created on the right, some cells away. I don't want at this point the operator to make any changes to this table/cells but only work on the original cells on the left. Is there a simple way to do something like this at the end of the Macro?

Selection.Protect

Where "Selection" is the cells I want to be uneditable. Furthermore, when the operator will modify the data on the left and restart the Macro I don't want the "protection" to block the creation of the new table in the protected cells. SO is there a way to put at the beginning of the Macro something like this?

Selection.Unprotect

Where "Selection" is the cells that the operator must not be able to modify out of the macro. I don't need anything particularly fancy, as easy as possible so my brain can understand it.

Much appreciated!

Edit for reply to CHill60

Thank you guys and CHill for the replies. I tried to integrate CHill's code and now a funny thing happens, all the cells are locked but the ones that I'd like to have uneditable and the one that are editable.

This is the code, surely I made some mistakes...

    Sub Create_End_of_Cable_List()

'Naming my sheet
    ActiveSheet.Name = "End of Cable List"

'Part of CHilli code starts here. Didn't want to use new Subs and just integrate the code in mine.
'Had to change from ThisWorkbook to ActiveWorkbook otherwise I would get an error
    With ActiveWorkbook.Sheets("End of Cable List").Unprotect
    
'Selecting the editable cells
    Range("A1").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Application.CutCopyMode = False
    
'Part of CHilli's code where instead of a range of cells I used the Selection
        Dim myEditRange As Range
        Set myEditRange = Selection 'This is your user entry space

        myEditRange.Locked = True

        ActiveWorkbook.Sheets("End of Cable List").Protect
        

    End With
    'Sheet "End of Cable List" is now completely locked down

    With ActiveWorkbook.Sheets("End of Cable List")
    
'Other part of CHilli's code
    ActiveWorkbook.Sheets("End of Cable List").Unprotect
        
'Selecting again the editable cells
    Range("A1").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Application.CutCopyMode = False
    
'I put a 2 after the myEditRange because was giving an error if named as above (probably because I'm not using two different Subs)
        Dim myEditRange2 As Range
        Set myEditRange2 = Selection

        myEditRange2.Locked = False

'Just clearing an interval where a possibly before created table could be
    Range("O1:T100").Select
    Selection.Clear
    
'Selecting again the editable cells in order to copy them and pasting where I want the uneditable table to be place
    Range("A1").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Application.CutCopyMode = False
    Selection.Copy
    
'Placing the uneditable table
    Range("O1").Select
    ActiveSheet.Paste
    
'Formatting the table
    Range("O1").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Application.CutCopyMode = False
    ActiveSheet.ListObjects.Add(xlSrcRange, Selection, , xlYes).Name = _
        "Table4"
    ActiveSheet.ListObjects("Table4").TableStyle = "TableStyleLight15"
        With Selection
            .HorizontalAlignment = xlGeneral
            .VerticalAlignment = xlCenter
            .WrapText = False
            .Orientation = 0
            .AddIndent = False
            .IndentLevel = 0
            .ShrinkToFit = False
            .ReadingOrder = xlContext
            .MergeCells = False
        End With
    
        With Selection
            .HorizontalAlignment = xlCenter
            .VerticalAlignment = xlCenter
            .WrapText = False
            .Orientation = 0
            .AddIndent = False
            .IndentLevel = 0
            .ShrinkToFit = False
            .ReadingOrder = xlContext
            .MergeCells = False
        End With
        
    
    Columns("O:O").ColumnWidth = 11
    Columns("P:P").ColumnWidth = 10
    Columns("Q:Q").ColumnWidth = 12
    Columns("R:R").ColumnWidth = 9
    Columns("S:S").ColumnWidth = 9
    Columns("T:T").ColumnWidth = 9
    
    
    Range("O1:T1").Font.Name = "Arial"
    Range("O1:T1").Font.Color = vbBlack
    Range("O1:T1").Font.Size = 10.5


    Range("O2:T2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Font.Name = "Arial"
    Selection.Font.Color = vbBlack
    Selection.Font.Size = 10
    
    
'Last part of CHilli's code
    ActiveWorkbook.Sheets("End of Cable List").Protect

    End With
    'Sheet "End of Cable List" is now completely locked down EXCEPT for where we have un Locked cells
    
End Sub

Upvotes: 0

Views: 155

Answers (1)

CHill60
CHill60

Reputation: 2033

CLR has hinted at the solution. Basically, any cells that are "Locked" will only be locked down if you Protect the worksheet. Locked is the default setting for Excel, so protecting a sheet normally locks down everything in it.

If you want to leave certain cells available for input then you must remove the Locked status. Manually you would do this with right-click, Format Cells, Protection, Untick Locked. Vice versa to lock them again

The following code gives you examples of how you might do that in VBA

Public Sub demoLockDownCells()

    With ThisWorkbook.Sheets(1)
    
        ThisWorkbook.Sheets(1).Unprotect
    
        Dim myEditRange As Range
        Set myEditRange = .Range("C5:E12") 'This is your user entry space

        myEditRange.Locked = True

        ThisWorkbook.Sheets(1).Protect

    End With
    'Sheet 1 is now completely locked down

End Sub
Public Sub demoUnLockCells()

    With ThisWorkbook.Sheets(1)
    
        ThisWorkbook.Sheets(1).Unprotect
    
        Dim myEditRange As Range
        Set myEditRange = .Range("C5:E12")

        myEditRange.Locked = False

        ThisWorkbook.Sheets(1).Protect

    End With
    'Sheet 1 is now completely locked down EXCEPT for where we have un Locked cells

End Sub

The important thing to note is the inter-relationship between protecting a sheet and locking a cell

Upvotes: 2

Related Questions