Changing Cells depending on data entered in other cells

[farida.lukmanji]

i'm trying to create a spreadsheet where depending on what data is entered in one cell thats how the other cells get changed, that is, gray out and disabled. i have the following vbscript coded so far but this could be totally incorrect, it's just not working......any help will be appreciated.

Private Sub Worksheet_Change(ByVal Target As Range)

If ActiveCell.Column = 5 And ActiveCell.Row >= 7 Then
If Target.Value = "X" Then
ActiveSheet.Unprotect Password:="MyPassword"
With ActiveCell.Offset(0, 5)
.ClearContents
.Interior.Color = RGB(192, 192, 192)
.Locked = True
End With
ActiveSheet.Protect Password:="MyPassword"
Else
If Target.Value = "" Then
ActiveSheet.Unprotect Password:="MyPassword"
With ActiveCell.Offset(0, 5)
.ClearContents
.Interior.Color = RGB(255, 255, 255)
.Locked = False
End With
ActiveSheet.Protect Password:="MyPassword"
End If
End If
ElseIf ActiveCell.Column = 7 And ActiveCell.Row >= 7 Then
If Target.Value = "X" Then
ActiveSheet.Unprotect Password:="MyPassword"
With ActiveCell.Offset(0, 3)
.ClearContents
.Interior.Color = RGB(192, 192, 192)
.Locked = True
End With
ActiveSheet.Protect Password:="MyPassword"
Else
If Target.Value = "" Then
ActiveSheet.Unprotect Password:="MyPassword"
With ActiveCell.Offset(0, 3)
.ClearContents
.Interior.Color = RGB(255, 255, 255)
.Locked = False
End With
ActiveSheet.Protect Password:="MyPassword"
End If
End If
End If

End Sub

Thanking you in advance,
Farida

 

[Tim Williams]

Some comments:
1. Since your code updates the sheet, you should turn off events
before doing the update since that will again fire off your
worksheet_change event
2. "Target" can be multiple cells, so you need to check each of them
in a loop
3. Make sure you unlock all the cells in columns you want people to
be able to edit: since the default state is Locked as soon as you
protect the sheet they will also be protected from edits

Some code ideas:

Private Sub Worksheet_Change(ByVal Target As Range)

Dim theCell As Range, theCol As Long, c As Range

For Each c In Target.Cells 'can be more than one cell

theCol = c.Column
If c.Row >= 7 Then
If theCol = 5 Or theCol = 7 Then
'what offset are we using?
Set theCell = c.Offset(0, IIf(theCol = 5, 5, 3))

If UCase(c.Value) = "X" Then
ProcessCell theCell, True
ElseIf c.Value = "" Then
ProcessCell theCell, False
End If

End If 'col check
End If 'row check

Next c 'next cell in Target if >1

End Sub

'Set the color and Locked property of cell "rng"
Private Sub ProcessCell(rng As Range, IsLocked As Boolean)
On Error GoTo haveError
Application.EnableEvents = False
rng.Parent.Unprotect Password:="MyPassword"
With rng
.ClearContents
.Locked = IsLocked
.Interior.Color = IIf(IsLocked, RGB(192, 192, 192), _
RGB(255, 255, 255))
End With
rng.Parent.Protect Password:="MyPassword"

haveError:
'make sure this is always reset to True
Application.EnableEvents = True
End Sub


Tim