Results 1 to 6 of 6
  1. #1
    3 Star Lounger
    Join Date
    Aug 2004
    Posts
    361
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Protecting Column or Row (Excel 2002)

    The following protects a workbook.
    Sub LockedBook()
    Dim ws As Worksheet
    For Each ws In ThisWorkbook.Worksheets
    ws.Protect Password:="yankeezulu", Contents:=True

    Next
    End Sub

    How would I modify this to protect a column or row in order to prevent anyone from changing formulas, or accidently overwriting information. Would it be as simple as entering something like ColumnŠ:= true after the Contents:=true

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Protecting Column or Row (Excel 2002)

    Protection is a property of the worksheet.

    Unlock any cells [format -cells - protection(tab)] you want the user to be able to edit (they are locked by default). The protect the worksheet and only the unlocked cells can be edited. All others are protected.

    If you want (before you protect), you can also hide the cells [again in format -cells - protection(tab)] (they are not hidden by default) that you do not want the value to display in the formula bar. and when you protect the sheet, you can see those values, but if you select the cell, you will not see the formula.

    Steve

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Protecting Column or Row (Excel 2002)

    By default, all cells in a worksheet are locked; this only becomes apparent when you protect the worksheet.
    You can unlock specific cells (before locking the worksheet) by selecting them and choosing Format | Cells... and activating the Protection tab. You can clear the Locked check box.
    Users won't be able to change the contents (values, formulas) of locked cells in a protected worksheet.

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Protecting Column or Row (Excel 2002)

    Using your code, to protect column C on all worksheets in the active workbook, use:

    <code>
    Option Explicit

    Sub LockedColumn()
    Dim ws As Worksheet
    For Each ws In ThisWorkbook.Worksheets
    ws.Cells.Locked = False
    ws.Range("C:C").Locked = True
    ws.Protect Password:="yankeezulu", Contents:=True
    Next
    End Sub
    </code>
    Legare Coleman

  5. #5
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: Protecting Column or Row (Excel 2002)

    Hi kiazd,

    An alternative approach would be to trap attempted changes via the worksheet change event. If you place the following code in the relevant worksheet module, it will prevent any changes to Column C and give a message to that effect if you attempt to do so.

    Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, ActiveSheet.Range("C:C")) Is Nothing Then Exit Sub
    Application.EnableEvents = False
    MsgBox "You can't alter cells in Column C.", vbExclamation + vbOKOnly
    Application.Undo
    Application.EnableEvents = True
    End Sub

    Note, though, that a user could defeat this by disabling macros.

    Cheers
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  6. #6
    3 Star Lounger
    Join Date
    Aug 2004
    Posts
    361
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Protecting Column or Row (Excel 2002)

    Thanks to all who answered. I will be trying the various methods and codes.

    It is great to be able to get the kind of help this site provides.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •