Clearing Cell Contents / Worksheet_Change Event

[Grahame Coyle]

I'm using the following code within a Worksheet_Change function to
lock/clear and unlock a range of cells. There's also another bit of code in
the function that forces uppercase for cell F3.

If I type "YES" into cell F3 then the cells unlock just fine, but there's a
problem when I try to clear the cell contents. If I select the cell and
press "Delete" the contents ("YES") disappear but the Change event isn't
triggered and the range D1016 don't lock. However, if I select the cell,
press Backspace then Enter then the Change event triggers and D10:d16 lock
just fine.

So it seems that the Delete key press doesn't constitute an Event. Is that
normal? Can anyone think of a workaround?

Using Excel 2003.

If Target.Address = "$F$3" And UCase(Target.Value) = "YES" Then
ActiveSheet.Unprotect Password:="password"
With Range("$D$10:$D$16")
.Locked = False
End With
ActiveSheet.Protect Password:="jess"
ElseIf Target.Address = "$F$3" And UCase(Target.Value) <> "YES" Then
ActiveSheet.Unprotect Password:="password"
With Range("$D$10:$D$16")
.ClearContents
.Locked = True
End With
ActiveSheet.Protect Password:="jess"
End If


Thanks in advance.

Grahame

 

[Dave Peterson]

Did you mean to change the password? You unprotect it with "password", but
reprotect it with "jess".

Maybe you have error handling (on error resume next) that hides the error?

And you'll want to stop excel from firing the _change event when you use
..clearcontents:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Dim myPWD As String

myPWD = "jess"

'one cell at a time
If Target.Cells.Count > 1 Then
Exit Sub
End If

If Target.Address = "$F$3" And UCase(Target.Value) = "YES" Then
Me.Unprotect Password:=myPWD
With Me.Range("$D$10:$D$16")
.Locked = False
End With
Me.Protect Password:=myPWD
ElseIf Target.Address = "$F$3" And UCase(Target.Value) <> "YES" Then
Me.Unprotect Password:=myPWD
With Me.Range("$D$10:$D$16")
Application.EnableEvents = False
.ClearContents
Application.EnableEvents = True
.Locked = True
End With
Me.Protect Password:=myPWD
End If

End Sub

Another version:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Dim myPWD As String

myPWD = "jess"

'one cell at a time
If Target.Cells.Count > 1 Then
Exit Sub
End If

On Error GoTo ErrHandler:

If Target.Address = "$F$3" Then
Me.Unprotect Password:=myPWD
With Me.Range("$D$10:$D$16")
If UCase(Target.Text) = "YES" Then
.Locked = False
Else
Application.EnableEvents = False
.ClearContents
.Locked = True
End If
End With
Me.Protect Password:=myPWD
End If

ErrHandler:
Application.EnableEvents = True

End Sub

 

[Grahame Coyle]

Hi Dave

Ooops, I edited the password for posting on the forum, but missed those two
entries. The actual code has "jess" in all the right places.

My Worksheet_Change code has the Application.EnableEvents True/False code
lines at the start and the end, the code block I posted is just an extract
from the middle of the function. I also have the "If Target.Cells.Count > 1
Then Exit Sub" code in the function already, I just didn't include it here
as it appears nearer the top of the function.

You're code is a bit neater than mine, but I still have the problem with
DELETING the contents of the cell not triggering the Change event. I've
added a cell comment to say that users have to use BACKSPACE then ENTER to
clear the cell, but I'm trying to make it really simple and not ask users to
go through some uncommon key presses to clear the cell as my target group
might not read the comments. Even using DELETE then ENTER to clear cell F3
doesn't seem to trigger the Change event. Maybe that's a "feature".

Cheers

Grahame

 

[Dave Peterson]

Create a new worksheet and paste that other code in the worksheet's module.

Then test it by typing Yes and then clearing F3.

Does it work ok? It worked fine for me.

I think that the trouble lies in the code you didn't share.

 

[Grahame Coyle]

Hi Dave

You're right, it works just fine. OK, here's the rest of the code, would
you have any clues as to where the conflict might be?

Thanks for all your help BTW.

Grahame


Private Sub Worksheet_Change(ByVal Target As Range)

If Intersect(Target,
Range("$B$6,$F$3,$F$6,$P$6,$B$10:$B$16,$C$10:$C$16,$D$10:$D$16")) Is Nothing
Then Exit Sub

If Target.Cells.Count > 1 Then
Exit Sub
End If

Application.EnableEvents = False

If Application.WorksheetFunction.IsText(Target.Value) Then
Target.Value = UCase(Target.Value)
End If

If Target.Address = "$F$3" And UCase(Target.Value) = "YES" Then
ActiveSheet.Unprotect Password:="jess"
Range("$F$3").Interior.Color = RGB(204, 255, 204) ' Pale Green
Range("$M$4").Interior.Color = RGB(204, 255, 204) ' Pale Green
With Range("$D$10:$D$16")
.Locked = False
.Interior.Color = RGB(204, 255, 204) ' Pale Green
End With
ActiveSheet.Protect Password:="jess"
ElseIf Target.Address = "$F$3" And UCase(Target.Value) <> "YES" Then
ActiveSheet.Unprotect Password:="jess"
Range("$F$3").Interior.Color = RGB(255, 255, 255) ' White
Range("$M$4").Interior.Color = RGB(221, 221, 221) ' Pale Grey
With Range("$D$10:$D$16")
.ClearContents
.Locked = True
.Interior.Color = RGB(128, 128, 128) ' Dark Grey
End With
ActiveSheet.Protect Password:="jess"
End If

If Target.Address = "$F$6" And Target.Value <> vbNullString Then
ActiveSheet.Name = Range("$F$6")
End If

If Target.Address = "$P$6" And Range("$T$6").Value = "EQUITY" Then
ActiveSheet.Unprotect Password:="jess"
With Range("$S$10:$S$16")
.Locked = False
.Interior.Color = RGB(204, 255, 204) ' Pale Green
End With
Range("$U$27:$W$28").Interior.Color = RGB(221, 221, 221) ' Pale Grey
ActiveSheet.Protect Password:="jess"
ElseIf Target.Address = "$P$6" And Range("$T$6").Value <> "EQUITY" Then
ActiveSheet.Unprotect Password:="jess"
With Range("$S$10:$S$16")
.ClearContents
.Locked = True
.Interior.Color = RGB(128, 128, 128) ' Dark Grey
End With
Range("$U$27:$W$28").Interior.Color = RGB(128, 128, 128) ' Dark Grey
ActiveSheet.Protect Password:="jess"
End If

Application.EnableEvents = True

End Sub

 

[Dave Peterson]

Your code worked fine for me in a brand new worksheet.

Have you tried that?

What happens when you select F3 and hit the delete key on the keyboard?

Hi Dave

You're right, it works just fine. OK, here's the rest of the code, would
you have any clues as to where the conflict might be?

Thanks for all your help BTW.

Grahame

<<snipped>>