Worksheet Change event code moved to Worksheet Calculate event... and it's not working

Hi,
After learning that the Worksheet Change Event doesn't alway trigger... I
added a formula to my worksheet the will calculate the cells that are being
changed...and I have moved this code below from the Worksheet_Change event
to the Worksheet_Calculate event inorder to get it to trigger every time a
cell is changed.
This code was working great in the Worksheet Change Event (just not all the
time), but now when I run it from the Worksheet Calculate event as shown
below..
I get an error message stating "Object Required" and it highlights this part
of the code (below) after clicking Debug :
If Not Application.Intersect(Target, _
Range("A8:A1000")) Is Nothing Then

I'm not sure why it's doing that....

********************************

Private Sub Worksheet_Calculate()
Dim prevSheet As Worksheet

With Me

If .Index = 1 Then
MsgBox "No sheets to the left"
Set prevSheet = Worksheets("Adjustments")
Else
Set prevSheet = Worksheets(.Index - 1)
End If

.Unprotect Password:="test"

If Not Application.Intersect(Target, _
Range("A8:A1000")) Is Nothing Then
prevSheet.Range("A13:A100").ClearContents
prevSheet.Unprotect Password:="test"
gCopyUnique Range("A8:A1000"), prevSheet.Range("A13")
End If

.Unprotect Password:="test"

'Range("R16:R51").Select
prevSheet.Unprotect Password:="test"
prevSheet.Range("A13:A47").Sort , _
Key1:=prevSheet.Range("A13"), _
Order1:=xlAscending, _
Header:=xlGuess, _
OrderCustom:=1, _
MatchCase:=False, _
Orientation:=xlTopToBottom
.Protect Password:="test", DrawingObjects:=True, _
Contents:=True, Scenarios:=True

End With
prevSheet.Protect Password:="test", DrawingObjects:=True, _
Contents:=True, Scenarios:=True
Application.ScreenUpdating = Ture

End Sub


Any help would be greatly appreciated!!
Thanks in advance!
Kimberly

Kimberley

There are no arguments passed to the Calculate event, so Target doesn't exist.

What do you actually want the code to do and when do you want it triggered.

That is because the event is not triggered by changes to a range, so there
is no Target, it is triggered by worksheet calculation, which could be many
cells.

You need to identify what condition (say a cell breaching a threshold) you
want to trigger your code, and then add that into the calculate event.

But more to the point, what makes you think Worksheet Change doesn't always
trigger?

--
HTH

Bob Phillips

"KimberlyC" <[email protected]> wrote in message
news:%[email protected]...
> Hi,
> After learning that the Worksheet Change Event doesn't alway trigger... I
> added a formula to my worksheet the will calculate the cells that are
being
> changed...and I have moved this code below from the Worksheet_Change event
> to the Worksheet_Calculate event inorder to get it to trigger every time a
> cell is changed.
> This code was working great in the Worksheet Change Event (just not all
the
> time), but now when I run it from the Worksheet Calculate event as shown
> below..
> I get an error message stating "Object Required" and it highlights this
part
> of the code (below) after clicking Debug :
> If Not Application.Intersect(Target, _
> Range("A8:A1000")) Is Nothing Then
>
> I'm not sure why it's doing that....
>
> ********************************
>
> Private Sub Worksheet_Calculate()
> Dim prevSheet As Worksheet
>
> With Me
>
> If .Index = 1 Then
> MsgBox "No sheets to the left"
> Set prevSheet = Worksheets("Adjustments")
> Else
> Set prevSheet = Worksheets(.Index - 1)
> End If
>
> .Unprotect Password:="test"
>
> If Not Application.Intersect(Target, _
> Range("A8:A1000")) Is Nothing Then
> prevSheet.Range("A13:A100").ClearContents
> prevSheet.Unprotect Password:="test"
> gCopyUnique Range("A8:A1000"), prevSheet.Range("A13")
> End If
>
> .Unprotect Password:="test"
>
> 'Range("R16:R51").Select
> prevSheet.Unprotect Password:="test"
> prevSheet.Range("A13:A47").Sort , _
> Key1:=prevSheet.Range("A13"), _
> Order1:=xlAscending, _
> Header:=xlGuess, _
> OrderCustom:=1, _
> MatchCase:=False, _
> Orientation:=xlTopToBottom
> .Protect Password:="test", DrawingObjects:=True, _
> Contents:=True, Scenarios:=True
>
> End With
> prevSheet.Protect Password:="test", DrawingObjects:=True, _
> Contents:=True, Scenarios:=True
> Application.ScreenUpdating = Ture
>
> End Sub
>
>
> Any help would be greatly appreciated!!
> Thanks in advance!
> Kimberly
>
>

"Norie" <[email protected]> wrote in
message news:[email protected]...
>
> Kimberley
>
> There are no arguments passed to the Calculate event, so Target doesn't
> exist.
>
> What do you actually want the code to do and when do you want it
> triggered.
>
>
> --
> Norie
> ------------------------------------------------------------------------
> Norie's Profile:
http://www.excelforum.com/member.php...o&userid=19362
> View this thread: http://www.excelforum.com/showthread...hreadid=381634
>

Thanks...
The worksheet change event doesn't work when I delete data from cells...

"Bob Phillips" <[email protected]> wrote in message
news:[email protected]...
> That is because the event is not triggered by changes to a range, so there
> is no Target, it is triggered by worksheet calculation, which could be
many
> cells.
>
> You need to identify what condition (say a cell breaching a threshold) you
> want to trigger your code, and then add that into the calculate event.
>
> But more to the point, what makes you think Worksheet Change doesn't
always
> trigger?
>
> --
> HTH
>
> Bob Phillips
>
> "KimberlyC" <[email protected]> wrote in message
> news:%[email protected]...
> > Hi,
> > After learning that the Worksheet Change Event doesn't alway trigger...
I
> > added a formula to my worksheet the will calculate the cells that are
> being
> > changed...and I have moved this code below from the Worksheet_Change
event
> > to the Worksheet_Calculate event inorder to get it to trigger every time
a
> > cell is changed.
> > This code was working great in the Worksheet Change Event (just not all
> the
> > time), but now when I run it from the Worksheet Calculate event as shown
> > below..
> > I get an error message stating "Object Required" and it highlights this
> part
> > of the code (below) after clicking Debug :
> > If Not Application.Intersect(Target, _
> > Range("A8:A1000")) Is Nothing Then
> >
> > I'm not sure why it's doing that....
> >
> > ********************************
> >
> > Private Sub Worksheet_Calculate()
> > Dim prevSheet As Worksheet
> >
> > With Me
> >
> > If .Index = 1 Then
> > MsgBox "No sheets to the left"
> > Set prevSheet = Worksheets("Adjustments")
> > Else
> > Set prevSheet = Worksheets(.Index - 1)
> > End If
> >
> > .Unprotect Password:="test"
> >
> > If Not Application.Intersect(Target, _
> > Range("A8:A1000")) Is Nothing Then
> > prevSheet.Range("A13:A100").ClearContents
> > prevSheet.Unprotect Password:="test"
> > gCopyUnique Range("A8:A1000"), prevSheet.Range("A13")
> > End If
> >
> > .Unprotect Password:="test"
> >
> > 'Range("R16:R51").Select
> > prevSheet.Unprotect Password:="test"
> > prevSheet.Range("A13:A47").Sort , _
> > Key1:=prevSheet.Range("A13"), _
> > Order1:=xlAscending, _
> > Header:=xlGuess, _
> > OrderCustom:=1, _
> > MatchCase:=False, _
> > Orientation:=xlTopToBottom
> > .Protect Password:="test", DrawingObjects:=True, _
> > Contents:=True, Scenarios:=True
> >
> > End With
> > prevSheet.Protect Password:="test", DrawingObjects:=True, _
> > Contents:=True, Scenarios:=True
> > Application.ScreenUpdating = Ture
> >
> > End Sub
> >
> >
> > Any help would be greatly appreciated!!
> > Thanks in advance!
> > Kimberly
> >
> >
>
>

Here is what the code does....a previous post I made:

I'm using the following code below (which is placed in the Workheet - Change
Module of the active worksheet) to create a list of codes on the previous
worksheet from the active worksheet. If a code is repeated numerous times on
the active worksheet, it only gets listed once on the previous worksheet.
It's working good, except when I press the delete key to remove codes..the
list on the previous worksheet does not change.. infact if I delete the
entire list on the active worksheet, there is no change on the previous
worksheet..the codes are still there..when they should be gone since I
deleted them from the active worksheet...therefore the worksheet change
event does not trigger by seleting data from cells.

How can I get this code to run when the user just presses the delete key to
remove codes on the activeworksheet?
Using the delete key seems to not trigger the worksheet change code to run..



"Norie" <[email protected]> wrote in
message news:[email protected]...
>
> Kimberley
>
> There are no arguments passed to the Calculate event, so Target doesn't
> exist.
>
> What do you actually want the code to do and when do you want it
> triggered.
>
>
> --
> Norie
> ------------------------------------------------------------------------
> Norie's Profile:
http://www.excelforum.com/member.php...o&userid=19362
> View this thread: http://www.excelforum.com/showthread...hreadid=381634
>