+ Reply to Thread
Results 1 to 6 of 6

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

  1. #1
    KimberlyC
    Guest

    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



  2. #2
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,626
    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.

  3. #3
    Bob Phillips
    Guest

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

    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" <kimberchia@sbcglobal.net> wrote in message
    news:%23nFOveAeFHA.2500@TK2MSFTNGP10.phx.gbl...
    > 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
    >
    >




  4. #4
    KimberlyC
    Guest

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


    "Norie" <Norie.1r39uh_1119546326.5194@excelforum-nospam.com> wrote in
    message news:Norie.1r39uh_1119546326.5194@excelforum-nospam.com...
    >
    > 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
    >




  5. #5
    KimberlyC
    Guest

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

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

    "Bob Phillips" <phillips@tiscali.co.uk> wrote in message
    news:OtGwmZBeFHA.796@TK2MSFTNGP09.phx.gbl...
    > 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" <kimberchia@sbcglobal.net> wrote in message
    > news:%23nFOveAeFHA.2500@TK2MSFTNGP10.phx.gbl...
    > > 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
    > >
    > >

    >
    >




  6. #6
    KimberlyC
    Guest

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

    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" <Norie.1r39uh_1119546326.5194@excelforum-nospam.com> wrote in
    message news:Norie.1r39uh_1119546326.5194@excelforum-nospam.com...
    >
    > 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
    >




+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1