PDA

View Full Version : Activating a column based in the other column value



sindhuja
09-29-2011, 11:38 AM
Hello...

can this be done using formula..

Based on values in Column C, Column E
should be unlocked. For example, if the value in C3 is Yes, E3
should be unlocked but should be locked for all other values of
C3. Similar for any input in Column C, Column E should be locked / unlocked
as approriate.

Can you please help me with this

-Sindhuja

Bob Phillips
09-29-2011, 12:07 PM
Try this event code

[


Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Cells.Count > 1 Then Exit Sub

If Not Intersect(Target, Me.Columns(3)) Is Nothing Then

Target.Offset(0, 2).Locked = Target.Value2 <> "Yes"
End If
End Sub

chensi
09-29-2011, 06:18 PM
good job.happy everyday please.


_________________________________________________________________
I live my life in colour and see in soundMake sure you are heard!

(http://www.wly.com/)

sindhuja
09-30-2011, 08:45 PM
Thanks for the coding.

Am able to edit column E even if the cell in column C is blank or any other value.

-Sindhuja

frank_m
10-01-2011, 12:27 AM
Locking a cell won't prevent editing. You additionally need to Protect the sheet.

I'm a bit of a laymen at writing code, but by mirroring some of the basics XLD provided, here's my stab at it.

Seems to work fine in my testing.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

With Target

If .Cells.Count > 1 Then Exit Sub

ActiveSheet.Unprotect

Me.Columns(3).Locked = False ' Comment this out if you want Column C to be protected

If Not Intersect(Target, Me.Columns(5)) Is Nothing Then

If UCase(.Offset(0, -2).Value2) = "YES" Then

.Locked = False

Else

.Locked = True

End If
End If

End With

ActiveSheet.Protect

End Sub

Bob Phillips
10-01-2011, 04:59 AM
The code only locks/unlocks column E if you make a change in column C.

ianswer
10-03-2011, 01:27 PM
Great answers ... appreciated

sindhuja
10-08-2011, 02:49 AM
Sorry.. still am not able to get the output... am not sure where am going wrong...

I copied the coding in the worksheet.

-Sindhuja