none
i want to unlock cells a16:c43 only if any value is entered in cell K8 RRS feed

  • Question

  • i want to unlock cells a16:c43 only if any value is entered in cell K8, please help me with this isuue
    Wednesday, February 22, 2017 1:36 PM

Answers

  • thanks it working perfect ...  thanks a lot... one more doubt , is there any way to display message when clicking a locked cell like (" fill the above fields first")
    • Marked as answer by DEEPU1980 Thursday, February 23, 2017 7:17 AM
    Thursday, February 23, 2017 7:16 AM

All replies

  • Right-click the sheet tab.

    Select 'View Code' from the context menu.

    Copy the following code into the worksheet module:

    Private Sub Worksheet_Change(ByVal Target As Range)
        If Not Intersect(Range("K8"), Target) Is Nothing Then
            Me.Unprotect Password:="secret"
            Range("A16:C43").Locked = (Range("K8").Value = "")
            Me.Protect Password:="secret"
        End If
    End Sub

    Change the password as needed, or remove it.

    Switch back to Excel.

    Make sure that you save the workbook in a format that supports macros (.xlsm, .xlsb or .xls, but not .xlsx).


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

    Wednesday, February 22, 2017 3:20 PM
  • Range("A16:C43").Locked = (Range("K8").Value = "")

    runtime error 1004 unable to set range property of the locked class

    this message is coming

    Wednesday, February 22, 2017 3:45 PM
  • Sorry, I can't explain that - the code works for me.

    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)

    Wednesday, February 22, 2017 4:27 PM
  • THANKS ITS WORKING FINE NOWCAN I ADD MORE CELLS FOR INPUTTING

    If Not Intersect(Range("K8"), Target) Is Nothing Then

    LIKE E12,D7,D10,K10 IF ALL THESE IS FILLED UNLOCK CELLS

    Wednesday, February 22, 2017 5:05 PM
  • CAN WE GIVE A MESSAGE WHEN CLICKING THE CELLS A16:C46 IF ITS LOCKED , PLEASE NETER ABOVE DATA IN k* FOR UNLOCKING
    Wednesday, February 22, 2017 5:07 PM
  • Please don't use ALL CAPS.

    Do you want to unlock the same range (A16:C43) whenever any of the cells E12, D7 etc. is filled? Or a different range for each?


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

    Wednesday, February 22, 2017 6:07 PM
  • same range..
    Wednesday, February 22, 2017 7:13 PM
  • Private Sub Worksheet_Change(ByVal Target As Range)
        If Not Intersect(Range("K8,E12,D7,D10,K10"), Target) Is Nothing Then
            Me.Unprotect Password:="secret"
            Range("A16:C43").Locked = _
                (Range("K8").Value = "") And _
                (Range("E12").Value = "") And _
                (Range("D7").Value = "") And _
                (Range("D10").Value = "") And _
                (Range("K10").Value = "")
            Me.Protect Password:="secret"
        End If
    End Sub


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

    Wednesday, February 22, 2017 8:59 PM
  • this is working like this if we enter a value in any of the cells K8,E12,D7,D10,K10 its unlocking the cells in range,  my plan is to unlock cells in range if all the cells K8,E12,D7,D10,K10 has value entered, if some cells value is missing don't unlock
    Thursday, February 23, 2017 4:49 AM
  • Change all four occurrences of And to Or in the code in my previous reply.

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

    Thursday, February 23, 2017 7:09 AM
  • thanks it working perfect ...  thanks a lot... one more doubt , is there any way to display message when clicking a locked cell like (" fill the above fields first")
    • Marked as answer by DEEPU1980 Thursday, February 23, 2017 7:17 AM
    Thursday, February 23, 2017 7:16 AM