Results 1 to 3 of 3

Thread: Securing cells

  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Jacksonville,NC, USA
    Posts
    705
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Of all the ways there are to protect cells, this one has me stumped...

    Is there a way to dynamically lock all cells that contain data? Example, the worksheet is entirely unlocked except as the data entry person enters a new record on a row. Once they press Enter and leave that record row line, the cells that have been typed in on the line automatically become locked and the ability to delete anything on that line that has been entered would then be up to the admin person who can unlock the area with text, values or whatever to make that change?

    I'm thinking it would be like a dynamic range (in concept only), just not sure how to approach it.

    I found the following How_to on a page somewhere but it's not working no matter how I try it (also I am using Excel 2007)

    First select all cells > Format cells > Protection > uncheck Locked
    in worksheet module

    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
    Const pw As String = "password"
    With Me
    .Unprotect pw
    Target.Locked = True
    .Protect pw
    End With
    End Sub

    Thank you
    NMP <img src=/S/cool.gif border=0 alt=cool width=15 height=15>

    If you can't convince them, confuse them. - Harry Truman <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Excel is not a database application - users aren't forced to work row by row ("record by record"). So the concept of "Once they press Enter and leave that record row line" isn't natural to Excel. You could lock each cell as it is filled, at the cost of losing the ability to undo changes (which might be too severe!):

    - Press Ctrl+A to select all cells.
    - Select Format | Cells...
    - Activate the Protection tab.
    - Clear the Locked check box.
    - Click OK.
    - Press Alt+F11 to activate the Visual Basic Editor.
    - Press Ctrl+G to activate the Immediate window.
    - Type the following line, then press Enter:

    ActiveSheet.Protect UserInterfaceOnly:=True

    - Double-click the sheet name in the Project Explorer on the left hand side.
    - Enter or copy/paste the following code into the sheet module:

    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
      Dim oCell As Range
      Application.EnableEvents = False
      For Each oCell In Target.Cells
    	If Not oCell.Value = "" Then
    	  oCell.Locked = True
    	End If
      Next oCell
      Application.EnableEvents = True
    End Sub

  3. #3
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Jacksonville,NC, USA
    Posts
    705
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='781304' date='23-Jun-2009 15:29']Excel is not a database application - users aren't forced to work row by row ("record by record"). So the concept of "Once they press Enter and leave that record row line" isn't natural to Excel. You could lock each cell as it is filled, at the cost of losing the ability to undo changes (which might be too severe!):

    - Press Ctrl+A to select all cells.
    - Select Format | Cells...
    - Activate the Protection tab.
    - Clear the Locked check box.
    - Click OK.
    - Press Alt+F11 to activate the Visual Basic Editor.
    - Press Ctrl+G to activate the Immediate window.
    - Type the following line, then press Enter:

    ActiveSheet.Protect UserInterfaceOnly:=True

    - Double-click the sheet name in the Project Explorer on the left hand side.
    - Enter or copy/paste the following code into the sheet module:

    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
      Dim oCell As Range
      Application.EnableEvents = False
      For Each oCell In Target.Cells
    	If Not oCell.Value = "" Then
    	  oCell.Locked = True
    	End If
      Next oCell
      Application.EnableEvents = True
    End Sub
    [/quote]

    I agree with the severity of doing something like this Hans, and will explain that fact to the student when I see her tomorrow. As Excel is a flat-file database of sorts and not the best way to keep a database I "do" explain when a true database would be better...and will again in this instance.

    My goal is to show them the answer, show them the value of wopr.com when they need help, and answer their question with a satisfactory response (effort put forth) and to not blow off any question because I think it's a bad idea. That concept will be exlained as I show her your response. It's highly likely she'll agree as I have instilled in them that answers given at wopr.com can be abided by...they are the written grail as far as I'm concerned, and when you say it's a bad idea, I will always agree. You are the gurus and I applaud you every day, and to my students I parlay this sentiment.

    Again, thank you
    NMP <img src=/S/cool.gif border=0 alt=cool width=15 height=15>

    If you can't convince them, confuse them. - Harry Truman <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>

Posting Permissions

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