Announcement

Collapse
No announcement yet.

Lock the cell value Column D based on the drop down list selection in Column C

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Lock the cell value Column D based on the drop down list selection in Column C



    Hi,

    I am the new learner of Excel VBA and I have searching many VBA Stuff's from our forum.

    My Requirement for this new thread is Lock the cell Column D based on the drop down selection in Column C.

    Please find the attached file for your reference. I would like to explain my requirement with more details.

    This attached excel file having some header Std.No(Column A),Std.Name(Column B),Pass/Fails(Column C) and Remarks(Column D).

    Column C (Pass/Fail) having drop down list Yes/No.

    If i select in column C value as "Yes" then particular Column D cell only locked automatically except to lock the whole sheet.

    For example: Column C2 value is Yes - Then lock the cell value only D2 and shade by some color. But, It should be editable for other cell values A2 and B2,C2.

    If i select in column C value as "No" then particular Column D cell is free text for giving some remarks.

    Immediate replys are very much appreciated.

    Regards,

    Arpu.
    Attached Files

  • #2
    Re: Lock the cell value Column D based on the drop down list selection in Column C

    When you state "lock" are you wanting the value in the adjacent cell to be protected from change?
    sigpicStreub

    "Zero to Hero and Back in 6 Seconds."

    Comment


    • #3
      Re: Lock the cell value Column D based on the drop down list selection in Column C

      Hi Streub,

      Thanks for your rapid reply. Yes. If i change the value in column C as Yes then Column D only locked. Other Columns such as Column A and Column B should be editable even Column C also.

      If you need any query regading this please feel free to ask me.

      Regards,

      Arputharaj

      Comment


      • #4
        Re: Lock the cell value Column D based on the drop down list selection in Column C

        Thank you for the clarification. Currently, the values in column "C" are "pass" and "fail".

        When you format a cell or range to "locked" it is open to edit unless the entire worksheet is protected.

        Is it your plan to add additional columns and rows in the very near future?

        Do you plan to enter your data directly to the worksheet or by means of a userform?

        Is your project to be distributed to others?

        A more detailed explanation as to this project would be most helpful. The only person I know who can read one's thoughts is my spouse.

        Try this code for the time. You should insert it the sheet 1 module and change your value to "YES" and "NO".
        Code:
        Private Sub Worksheet_Change(ByVal Target As Excel.Range)
            ActiveSheet.Protect
                If Target.Column = 3 Then
                    thisrow = Target.Row
                        If Target.Value = "YES" Then
                            ActiveSheet.Unprotect
                            Range("d" & thisrow).Interior.ColorIndex = 4
                             ActiveSheet.Protect
                Else
                
                If Target.Value = "NO" Then
                    ActiveSheet.Unprotect
                    Range("d" & thisrow).Interior.ColorIndex = 3
                    
                    End If
                End If
            End If
            
        End Sub
        Last edited by Streub; August 14th, 2013, 08:15.
        sigpicStreub

        "Zero to Hero and Back in 6 Seconds."

        Comment


        • #5


          Hi Streub,

          i see the in the above post no respond received to you quary on the question posted by Arputharaj,
          Currently i looking for the same solution, if you permit me to continue with the same thread.

          Comment

          Working...
          X