Thanks Thanks:  0
Likes Likes:  0
Results 1 to 3 of 3

Thread: VBA Lock and Protect cells or range of cells

  1. #1
    New Member
    Join Date
    Feb 2009
    Location
    Raleigh, NC
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default VBA Lock and Protect cells or range of cells

    Corporate edict.

    I have a worksheet that is locked and protected now, except for cells in a certain collumn. I have named the cells in that column "MS96A".

    If a user enters a date in a cell or range of cells anywhere in the column, the changed cells also need to be locked and protected (Once they enter a date, it is not allowed EVER to be changed again. Corporate requirement! *Shrug*).

    What I am looking for is this. If the user selects that cell again, they will get the usual pop-up message, "The cell or chart that you are trying to change is protected..."

    I think I am close, but I am getting an "End If without block If" error on the If Clause.


    Sub Worksheet_Change(ByVal Target As Excel.Range)

    Dim MRange As Range
    Set MRange = Range("MS96A")
    ' If Not Intersect(Target, MRange) Is Nothing Then
    For Each cell In MRange
    Sheets("Sheet1").Unprotect Password:="temp"
    cell.Interior.ColorIndex = 3
    cell.Font.Color = vbBlack
    Selection.Locked = True
    Selection.FormulaHidden = False
    Next cell
    ActiveSheet.Protect Password:="temp", _
    DrawingObjects:=False, _
    Contents:=True, _
    Scenarios:=False
    ActiveSheet.EnableSelection = xlUnlockedCells
    End Sub

  2. #2
    MrExcel MVP VoG's Avatar
    Join Date
    Jun 2002
    Location
    127.0.0.1
    Posts
    63,651
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    7 Thread(s)

    Default Re: VBA Lock and Protect cells or range of cells

    Should it be?

    Code:
    Sub Worksheet_Change(ByVal Target As Excel.Range)
    Dim MRange As Range
    Set MRange = Range("MS96A")
    If Not Intersect(Target, MRange) Is Nothing Then
        For Each cell In MRange
            Me.Unprotect Password:="temp"
            cell.Interior.ColorIndex = 3
            cell.Font.Color = vbBlack
            Selection.Locked = True
            Selection.FormulaHidden = False
        Next cell
    End If
    Me.Protect Password:="temp", DrawingObjects:=False, contents:=True, Scenarios:=False
    Me.EnableSelection = xlUnlockedCells
    End Sub
    HTH, Peter
    Please test any code on a copy of your workbook.

  3. #3
    New Member
    Join Date
    Feb 2009
    Location
    Raleigh, NC
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Lock and Protect cells or range of cells

    I had tried an End If, but it still wouldn't work.

    I just needed to remove the underscore at the end of the If statement.

    Thanks Greatly

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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

 

DMCA.com