Results 1 to 6 of 6
  1. #1
    Join Date
    Sep 2002
    Location
    NJ
    Posts
    139

    Unanswered: lock and unlock cell

    Hi,

    excel 2k
    windows2k

    I have an excel spreadsheet in which the user will click a commandbutton_click event to enter data either being copied from already existing data from another cell or entered manually. The cell, or range of cells, say: range("A1:C10"), are unlocked after the spreadsheet has been locked for all other cells. I want the cell to be locked right after the data is entered so that the user can not alter the data. Or is there a way to have the entire sheet locked and then when the user click the command button, the cell is unlocked, the data entered and then when the user presses enter, the cell is locked?

    Private Sub CommandButton1_Click()
    Dim i As Integer
    For i = 1 To 10
    Range("A" & i).Select
    Range("A" & i).Locked = False
    Range("A" & i).Value = Range("H" & i).Value
    Range("A" & i).Select
    Selection.Locked = True

    Range("c" & i).Select
    Selection.Locked = False
    Range("c" & i).Value = Date
    Range("c" & i).Select
    Selection.Locked = True
    'Range("c" & i).Locked = True
    Next i
    End Sub


    This does not work. Any help is greatly appreciated.

  2. #2
    Join Date
    Jan 2004
    Location
    Aberdeen, Scotland
    Posts
    1,067
    what i think you need to look at is the Worksheet_Change Event
    something like
    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
        ActiveSheet.Unprotect
        If Not Target.Locked Then
            If Not Target.Formula = "" Then
                Target.Locked = True
                ActiveSheet.Protect
            End If
        End If
    End Sub
    HTH
    Dave

  3. #3
    Join Date
    Oct 2003
    Posts
    1,091
    Leave the cell Locked at all times. The key is to unProtect the beginning of that section of the macro, then Protect at the end. This way you don't need to have a separate Private sub.
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  4. #4
    Join Date
    Sep 2002
    Location
    NJ
    Posts
    139

    lock cell in Excel

    Hi,

    neither option really works. Here is the code I want to execute:

    I want the sheet to be locked, but when clicking the button, then i want to be able to ulock that cell, apply the data and then lock the cell again so that it can not be touched.

    Private Sub CommandButton1_Click()
    Dim i As Integer
    For i = 1 To 10
    Range("a" & i).Value = "This is a test " & i
    Next i
    End Sub

  5. #5
    Join Date
    Oct 2003
    Posts
    1,091
    You don't need this in the Private code.

    Rather, put this in the general Module:
    Code:
    Sub CommandButton1_Click()
        Dim i As Integer
        ActiveSheet.Unprotect
        For i = 1 To 10
            Range("a" & i).Value = "This is a test " & i
        Next i
        ActiveSheet.Protect
    
    End Sub
    Then assign this macro to the commandbutton. If you want to have it password protected, then add appropriate items.

    I tried this and it works fine.
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  6. #6
    Join Date
    Sep 2002
    Location
    NJ
    Posts
    139

    lock cell

    HI,

    That works well if the whole sheet is to be protected. but my problem is that the user should be able to modify some cell and not 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
  •