none
Protect input after single cell entry within a range in MS Excel 2010 RRS feed

  • Question

  • Hello

    I have gone through the helpful answers on the topic and indeed it worked for me, but I want to only protect a range within the spreadsheet and not the entire spreadsheet, using the below code the entire spreadsheet is getting protected after one input. Lets say i want to protect only Cell range (H3:J250), then how can I do it?

    Private Sub Worksheet_Change(ByVal Target As Range)
        Dim cel As Range
        ActiveSheet.Unprotect Password:="secret"
        For Each cel In Target
            If cel.Value <> "" Then
                cel.Locked = True
            End If
        Next cel
        ActiveSheet.Protect Password:="secret"
    End Sub
                

    Thursday, August 27, 2015 11:54 AM

Answers

  • Like this:

    Private Sub Worksheet_Change(ByVal Target As Range)
        Dim cel As Range
        If Not Intersect(Range("H3:J250"), Target) Is Nothing Then
            Me.Unprotect Password:="secret"
            For Each cel In Intersect(Range("H3:J250"), Target)
                If cel.Value <> "" Then
                    cel.Locked = True
                End If
            Next cel
            Me.Protect Password:="secret"
        End If
    End Sub


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by Jawed Hussain Friday, August 28, 2015 9:00 AM
    Thursday, August 27, 2015 2:30 PM

All replies

  • Like this:

    Private Sub Worksheet_Change(ByVal Target As Range)
        Dim cel As Range
        If Not Intersect(Range("H3:J250"), Target) Is Nothing Then
            Me.Unprotect Password:="secret"
            For Each cel In Intersect(Range("H3:J250"), Target)
                If cel.Value <> "" Then
                    cel.Locked = True
                End If
            Next cel
            Me.Protect Password:="secret"
        End If
    End Sub


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by Jawed Hussain Friday, August 28, 2015 9:00 AM
    Thursday, August 27, 2015 2:30 PM
  • thanks Hans,

    I tried the above code, but the entire file is getting protected once I protect it using a password and doesn't even allow single edit on the target cells?

    Tuesday, September 1, 2015 11:33 AM
  • Unprotect the worksheet.

    Select all cells that the user should be able to enter data in.

    Press Ctrl+1 to activate the Format Cells dialog.

    Activate the Protection tab.

    Make sure that the Locked check box is clear.

    Click OK.

    Protect the sheet again.

    You should now be able to enter a value in the cells.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Tuesday, September 1, 2015 2:30 PM
  • Hello,

    I'm not sure if it's too late to get a reply from you Hans Vogelaar but I'm hoping you will reply

    The above code you have written is great. It does the job I need it to do perfectly.

    However, I was wonder if we could add another feature. Ideally I would like that when I click back on cell that has just been locked, instead of it popping up with a textbox, saying this file is protected please unprotect if you want to change, I would like it to come up with the option to put in a password to unprotect that individual cell then and there, make the necessary changes and for it to then lock itself again.

    Is what I am thinking of possible?

    Are you able to help me with this code?

    Many thanks

    Friday, March 18, 2016 3:50 PM
  • You could add the following code to the worksheet module. The user can double-click to edit a cell if they know the password.

    Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
        Const strPassword = "secret"
        If Not Intersect(Range("H3:J250"), Target) Is Nothing Then
            If Target.Value <> "" Then
                If InputBox("Enter the password to unlock the cell") = strPassword Then
                    Me.Unprotect Password:="secret"
                    Target.Locked = False
                    Me.Protect Password:="secret"
                Else
                    Cancel = True
                End If
            End If
        End If
    End Sub
    Warning: clicking in the formula bar or simply starting to type won't work.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Saturday, March 19, 2016 2:40 PM
  • Thank you very much!

    However, I keep getting this error message when I save the document:

    "Be careful! Parts of your document may include personal information that can't be removed by the Document Inspector"

    Can you help me understand what this means please?

    Also the formula doesn't seem to be working on my spreadsheet. I have typed in the code, saved the document, closed the document, opened it to make changes in the specified range, saved, closed and then opened again but I can still delete the data in the cell which is derived from a drop down list without needing a password

    Can you please help me again?

    Kind regards,

    Monday, March 21, 2016 2:51 PM
  • You will see that message if you let Excel remove personal details when you save the workbook (File > Options > Trust Center > Trust Center Settings > Privacy Options).

    Did you copy the code into the worksheet module? It won't work if you copy it into an ordinary module or into the ThisWorkbook module.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Monday, March 21, 2016 4:19 PM
  • I right clicked on the worksheet tab and selected View Code. I copied it into the code area of the individual worksheet.

    Should I type this instead?

    Monday, March 21, 2016 4:39 PM
  • Hi Hans,

    The error message no longer but the code is still not working and this after I have typed it out fully

    Are you able to advise on why this is not working for me?

    Many thanks

    Monday, March 21, 2016 5:33 PM
  • Could you create a stripped-down copy of the workbook (without sensitive information) and make it available through one of the websites that let you upload and share a file, such as FileDropper (http://filedropper.com) or DropBox (https://www.dropbox.com). Then post a link to the uploaded and shared file here.

    Or register at www.eileenslounge.com (it's free) and start a thread in the Excel forum. You can attach files up to 250 KB to a post there (zipped if necessary).


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Monday, March 21, 2016 9:47 PM
  • You're making things complicated by having a different layout each month. HQ Approved is in column L of the Jan 16 sheet, but in column N of the Feb 16 sheet.

    The code for Feb 16 could look like this:

    Option Explicit
    
    Const strPassword = "test"
    
    Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
        Const strRange = "A12:A94"
        If Not Intersect(Range(strRange), Target) Is Nothing Then
            If Target.Value <> "" Then
                If InputBox("Enter the password to unlock the cell") = strPassword Then
                    Me.Unprotect Password:=strPassword
                    Target.EntireRow.Locked = False
                    Me.Protect Password:=strPassword
                Else
                    Cancel = True
                End If
            End If
        End If
    End Sub
    
    Private Sub Worksheet_Change(ByVal Target As Range)
        Const strRange = "N12:N94"
        Dim cel As Range
        If Not Intersect(Range(strRange), Target) Is Nothing Then
            For Each cel In Intersect(Range(strRange), Target)
                If cel.Value <> "" Then
                    Me.Unprotect Password:=strPassword
                    cel.EntireRow.Locked = True
                    Me.Protect Password:=strPassword
                End If
            Next cel
        End If
    End Sub
    

    Double-clicking on a cell in column A will display a password dialog. (Double-clicking the HQ Approved column won't work because of the dropdown).

    I'm not sure this is what you want, but see https://www.dropbox.com/s/c2ct90v81k2e7bf/2015-16%20Q4%20Payment%20Run%20VS.xlsm?dl=1


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Tuesday, March 29, 2016 3:39 PM
  • Hi Hans,

    Thank you very much for this, this is amazing and work perfectly!

    Kind regards,

    Friday, April 1, 2016 12:57 PM
  • Hi Hans,

    After doing some user testing we have realised that a few of the columns need to be changed through different stages of the payment cycle. Therefore we would like to restrict the range that is locked to just columns A, B, C, D, E, F, G, H, I, L, N, O, & Q  of each payment row that is approved.

    Are you able to please advise me of what changes I need to do to the above code?

    Thank you again.

    Kind regards,

    Wednesday, April 6, 2016 3:07 PM
  • Change the line

                    cel.EntireRow.Locked = True

    in Worksheet_Change to

                    Dim r As Long
                    r = cel.Row
                    Range("A" & r & ":I" & r & ",L" & r & ",N" & r & _
                        ":O" & r & ",Q" & r).EntireRow.Locked = True


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Wednesday, April 6, 2016 4:13 PM
  • If the rows were locked by a previous version of the code, you will have to unlock them once manually.

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Thursday, April 7, 2016 7:27 PM
  • Hi Hans,

    I tried doing as you suggested but it hasn't worked.

    I even deleted the code, took away all passwords and then re-entered the code after saving, closing and opening but still all cells in the row remain locked.

    Do you have any other suggestions?

    Kind regards,

    Thursday, April 7, 2016 10:33 PM
  • Could you create a stripped-down copy of the workbook (without sensitive information) and make it available through one of the websites that let you upload and share a file, such as FileDropper (http://filedropper.com) or DropBox (https://www.dropbox.com). Then post a link to the uploaded and shared file here.

    Or register at www.eileenslounge.com (it's free) and start a thread in the Excel forum. You can attach files up to 250 KB to a post there (zipped if necessary).


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Friday, April 8, 2016 9:25 AM
  • You have placed a password on the VBA code. Could you either post the password, or upload a version in which the VBA code is unprotected?

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Monday, April 11, 2016 5:35 AM
  • I'm very sorry - I see now that it is my fault. The line

    Range("A" & r & ":I" & r & ",L" & r & ",N" & r & ":R" & r).EntireRow.Locked = True

    should have been

    Range("A" & r & ":I" & r & ",L" & r & ",N" & r & ":R" & r).Locked = True

    My apologies!


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Monday, April 11, 2016 3:01 PM
  • Thank you so much, you have been very helpful!

    Monday, April 11, 2016 4:11 PM