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

  • dev99
    replied
    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.

    Leave a comment:


  • Streub
    replied
    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.

    Leave a comment:


  • Arputharaj
    replied
    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

    Leave a comment:


  • Streub
    replied
    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?

    Leave a comment:

Working...
X