+ Reply to Thread
Results 1 to 3 of 3

Change event macro Question

  1. #1
    Martin
    Guest

    Change event macro Question

    I an analyzing costs on a worksheet. I would like to do a backward
    comparison using goal seek.
    My input for goal seek is in range "Mygoal"

    MyMacro runs fine when i run it from a button after putting my input value
    in "Mygoal". code is.
    Private Sub myMacro()
    Dim myvalue As Integer
    myvalue = Worksheets("Cost Sheet").Range("mygoal").Value
    Range("MyBillingRate").GoalSeek Goal:=myvalue,
    ChangingCell:=Range("MyAccountfactor")
    End Sub

    I would like to run Mymacro when I "enter" a value into range "Mygoal"
    can I do so through the change event? I
    --
    Martin

  2. #2
    Norman Jones
    Guest

    Re: Change event macro Question

    Hi Martin,

    Try:

    '============>>
    Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Count > 1 Then Exit Sub

    If Not Intersect(Target, Me.Range("MyGoal")) Is Nothing Then
    Call MyMacro
    End If

    End Sub
    '<<============

    This is worksheet event code and should be pasted into the Cost Sheets's
    code module (not a standard module and not the workbook's ThisWorkbook
    module):

    *******************************************
    Right-click the worksheet's tab

    Select 'View Code' from the menu and paste the code.

    Alt-F11 to return to Excel.
    *******************************************


    ---
    Regards,
    Norman


    "Martin" <[email protected]> wrote in message
    news:[email protected]...
    >I an analyzing costs on a worksheet. I would like to do a backward
    > comparison using goal seek.
    > My input for goal seek is in range "Mygoal"
    >
    > MyMacro runs fine when i run it from a button after putting my input value
    > in "Mygoal". code is.
    > Private Sub myMacro()
    > Dim myvalue As Integer
    > myvalue = Worksheets("Cost Sheet").Range("mygoal").Value
    > Range("MyBillingRate").GoalSeek Goal:=myvalue,
    > ChangingCell:=Range("MyAccountfactor")
    > End Sub
    >
    > I would like to run Mymacro when I "enter" a value into range "Mygoal"
    > can I do so through the change event? I
    > --
    > Martin




  3. #3
    Martin
    Guest

    Re: Change event macro Question

    Perfect thank you Norman
    --
    Martin


    "Norman Jones" wrote:

    > Hi Martin,
    >
    > Try:
    >
    > '============>>
    > Private Sub Worksheet_Change(ByVal Target As Range)
    >
    > If Target.Count > 1 Then Exit Sub
    >
    > If Not Intersect(Target, Me.Range("MyGoal")) Is Nothing Then
    > Call MyMacro
    > End If
    >
    > End Sub
    > '<<============
    >
    > This is worksheet event code and should be pasted into the Cost Sheets's
    > code module (not a standard module and not the workbook's ThisWorkbook
    > module):
    >
    > *******************************************
    > Right-click the worksheet's tab
    >
    > Select 'View Code' from the menu and paste the code.
    >
    > Alt-F11 to return to Excel.
    > *******************************************
    >
    >
    > ---
    > Regards,
    > Norman
    >
    >
    > "Martin" <[email protected]> wrote in message
    > news:[email protected]...
    > >I an analyzing costs on a worksheet. I would like to do a backward
    > > comparison using goal seek.
    > > My input for goal seek is in range "Mygoal"
    > >
    > > MyMacro runs fine when i run it from a button after putting my input value
    > > in "Mygoal". code is.
    > > Private Sub myMacro()
    > > Dim myvalue As Integer
    > > myvalue = Worksheets("Cost Sheet").Range("mygoal").Value
    > > Range("MyBillingRate").GoalSeek Goal:=myvalue,
    > > ChangingCell:=Range("MyAccountfactor")
    > > End Sub
    > >
    > > I would like to run Mymacro when I "enter" a value into range "Mygoal"
    > > can I do so through the change event? I
    > > --
    > > Martin

    >
    >
    >


+ 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