Microsoft Excel - Protect / unprotect one column only

Asked By Ken Brown on 10-Oct-09 05:14 PM


I am using Excel 2003. I have a macro to copy and paste data, selecting from a range and pasting into a row.

One of the columns in the worksheet needs to be protected. how do I only protect and unprotect only one column (how would the coding differ?)

The coding I have is:

Sub CopyPaste()
Sheets("Existing Card Entry").Unprotect

Sheets("Existing Card Entry").Range("part").ClearContents

    With Worksheets(1).Range("partdetail2")
        Set c = .Find(Worksheets("Existing Cards").Range("L2").Value, LookIn:=xlValues)  'this identifies the value D11 in worksheet called Summary1
        If Not c Is Nothing Then
            firstAddress = c.Address
                c.EntireRow.Copy Destination:=Worksheets("Existing Card Entry").Range("a" & Worksheets("Existing Card Entry").Range("a65536").End(xlUp).Row + 1)
                Set c = .FindNext(c)
             Loop While Not c Is Nothing And c.Address <> firstAddress
        End If
    End With
    Sheets("Existing Card Entry").Select
Sheets("Existing Card Entry").Protect
End Sub



Jonathan VH replied to Ken Brown on 10-Oct-09 08:46 PM

The granularity for the Protect method is no finer than a whole worksheet, so you'd need to keep the worksheet protected but unlock all cells other than those you want protected. I'm unsure how the code you posted relates to your question, but here's how to lock just one column:

Cells.Locked = False
Range("G:G").Locked = True

BTW, if you use the UserInterfaceOnly:=True option with the Protect method, your VBA code will be able to change the sheet without first unprotecting it.