Hi all. I have the following inside a worksheet change event:
'Lock/Unlock cells based on FT/PT status
If .Column = 5 And .Row 11 And .Row < 312 Then
Application.EnableEvents = False
If .Value = "PT" Then
Cells(.Row, 6).Value = 20
Else
Cells(.Row, 6).Value = 40
End If
Application.EnableEvents = True
End If

I the user highlights and clears contents of column 5 and 6 at the same time
(ie E5:F5), I get a type mismatch error on the line If .Value = "PT" Then,
and the change event won't fire unless I completely clode out of excel and
re-open. Any ideas on how to prevent that from happening?

First lets address the "Events stop firing" problem. Any time you modify the
applicatin level settings you really NEED to use an error handler. Something
like this...

On Error Goto ErrorHandler
If .Column = 5 And .Row 11 And .Row < 312 Then
Application.EnableEvents = False
If .Value = "PT" Then
Cells(.Row, 6).Value = 20
Else
Cells(.Row, 6).Value = 40
End If
End If

ErrorHandler:
Application.EnableEvents = True
End Sub

Now if an error happens the code will go to the error handler and turn the
events back on. Careful when debugging this kind of code also becuase if you
halt the exectuion prior to the application setting being reset you will run
into the same problem. When I am working on code similar to this I will
sometimes have a procedure which I will run to reset things back to normal.
Something like this...

Sub ResetApplication()
application.enableevents = true
application.screenupdating = true
application.calculation = xlautomatic
application.statusbar = false
application.displayalerts = true
End Sub

Instead of rebooting excel just run this and your world will be back to its
happy place.
--
HTH...

Jim Thomlinson


"Steph" wrote:

Hi all. I have the following inside a worksheet change event:
'Lock/Unlock cells based on FT/PT status
If .Column = 5 And .Row 11 And .Row < 312 Then
Application.EnableEvents = False
If .Value = "PT" Then
Cells(.Row, 6).Value = 20
Else
Cells(.Row, 6).Value = 40
End If
Application.EnableEvents = True
End If

I the user highlights and clears contents of column 5 and 6 at the same time
(ie E5:F5), I get a type mismatch error on the line If .Value = "PT" Then,
and the change event won't fire unless I completely clode out of excel and
re-open. Any ideas on how to prevent that from happening?

If they select the entire column, then .Row would not be between 11 and 312.
Nontheless, if you only want the changes made if a single cell has been
changed


'Lock/Unlock cells based on FT/PT status
On Error goto ErrHandler
if Target.count 1 then exit sub
If .Column = 5 And .Row 11 And .Row < 312 Then
Application.EnableEvents = False
If .Value = "PT" Then
Cells(.Row, 6).Value = 20
Else
Cells(.Row, 6).Value = 40
End If
End If

ErrHandler:
Application.EnableEvents = True


--
Regards,
Tom Ogilvy

"Steph" wrote in message
...
Hi all. I have the following inside a worksheet change event:
'Lock/Unlock cells based on FT/PT status
If .Column = 5 And .Row 11 And .Row < 312 Then
Application.EnableEvents = False
If .Value = "PT" Then
Cells(.Row, 6).Value = 20
Else
Cells(.Row, 6).Value = 40
End If
Application.EnableEvents = True
End If

I the user highlights and clears contents of column 5 and 6 at the same
time
(ie E5:F5), I get a type mismatch error on the line If .Value = "PT" Then,
and the change event won't fire unless I completely clode out of excel and
re-open. Any ideas on how to prevent that from happening?

As for the .Value throwing an error. I am assuming that you have "With
Target"... The problem is that target is a range, not a single cell. What you
can do is to traverse through all of the cells in the target and check if it
is within the range you have defined and then do the voodo you want to do.
Since I have seen your coding and I know you have a reasonable ability to do
this kind of thing I will give you some general instructions. What you want
to do is to check the intersection of the target with the the range set out
in your if statement. I recomend creating a range object for this

Dim rng as range
dim rngCurrent as range

On error resume next
set rng = intersect(Target, range("E12", "E311"))
on error goto errorhandler
if not rng is nothing then
for each rngCurrent in rng

next rngCurrent
--
HTH...

Jim Thomlinson


"Steph" wrote:

Hi all. I have the following inside a worksheet change event:
'Lock/Unlock cells based on FT/PT status
If .Column = 5 And .Row 11 And .Row < 312 Then
Application.EnableEvents = False
If .Value = "PT" Then
Cells(.Row, 6).Value = 20
Else
Cells(.Row, 6).Value = 40
End If
Application.EnableEvents = True
End If

I the user highlights and clears contents of column 5 and 6 at the same time
(ie E5:F5), I get a type mismatch error on the line If .Value = "PT" Then,
and the change event won't fire unless I completely clode out of excel and
re-open. Any ideas on how to prevent that from happening?

Thanks Jim. I added the error handler that you mentioned in your first
post, and that took care of both problems. The code no longer throws an
error when both cells are cleared, which in turn (I suppose) does not cause
the enableevents to go to false. Thanks so much for your help!

"Jim Thomlinson" wrote in message
...
As for the .Value throwing an error. I am assuming that you have "With
Target"... The problem is that target is a range, not a single cell. What
you
can do is to traverse through all of the cells in the target and check if
it
is within the range you have defined and then do the voodo you want to do.
Since I have seen your coding and I know you have a reasonable ability to
do
this kind of thing I will give you some general instructions. What you
want
to do is to check the intersection of the target with the the range set
out
in your if statement. I recomend creating a range object for this

Dim rng as range
dim rngCurrent as range

On error resume next
set rng = intersect(Target, range("E12", "E311"))
on error goto errorhandler
if not rng is nothing then
for each rngCurrent in rng

next rngCurrent
--
HTH...

Jim Thomlinson


"Steph" wrote:

Hi all. I have the following inside a worksheet change event:
'Lock/Unlock cells based on FT/PT status
If .Column = 5 And .Row 11 And .Row < 312 Then
Application.EnableEvents = False
If .Value = "PT" Then
Cells(.Row, 6).Value = 20
Else
Cells(.Row, 6).Value = 40
End If
Application.EnableEvents = True
End If

I the user highlights and clears contents of column 5 and 6 at the same
time
(ie E5:F5), I get a type mismatch error on the line If .Value = "PT"
Then,
and the change event won't fire unless I completely clode out of excel
and
re-open. Any ideas on how to prevent that from happening?