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
On Oct 20, 6:48*am, farida.lukma...@gmail.com wrote:
> 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
|
