+ Reply to Thread
Results 1 to 9 of 9

Problem with Running VBA code on Cell Change

  1. #1
    Marty
    Guest

    Problem with Running VBA code on Cell Change

    Hello:

    I'm using Excel 2003. I'm wanting to execute a series of code steps upon a
    change to any cell within a specified range of cells. From some searching, I
    found the following code example, which does what it says it's supposed to do:
    =====================================================
    'This procedure will change the value of F10 to TRUE if the value in A1 is
    changed
    'to a value greater than or equal to 10. Note that we change
    'Application.EnableEvents to False in order to prevent the Change event from
    'calling itself, and then restore the setting back to True.

    Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    If Target.Address = "$A$1" Then
    Application.EnableEvents = False
    If Target.Value >= 10 Then
    Range("F10").Value = True
    Else
    Range("F10").Value = False
    End If
    Application.EnableEvents = True
    End If
    End Sub
    =====================================================
    Seems simple enough. So I reasoned that I could delete the If-Else-End-If
    block and replace it with my code that I want to execute, and change the
    range from $A1$1 to the range of cells on which I want to activate my code if
    it changes. In my case, this is "E7:H31"

    I can't get it to work. Basically, I want to put the new value of DIST into
    cell J2 if any of AAA, BBB or CCC changes.

    Here is A SMALL PORTION of the code (NOTE: BBB, CCC and DDD are within the
    range of cells that I want to monitor and execute upon changes. AAA and MDC
    are only one of four fixed values and are outside the range. MDC is a
    constant, also outside the range):
    =====================================================
    Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    If Target.Address = "E7:H31" Then
    Application.EnableEvents = False
    Dim MYSHEET as Object
    Set MYSHEET = Workbooks(ActiveWorkbook.Name).Sheets("Sheet1")

    If AAA = 0 And BBB = "M" And CCC = "3D" And DDD = "M" Then
    DIST = {formula which contains AAA, BBB, CCC and DDD as variables}
    If DIST < 0 Then
    DIST = 0
    End If
    If DIST > MDC Then
    DIST = MDC
    End If
    MYSHEET.Cells(2, 10) = DIST
    End If

    Application.EnableEvents = True
    End If
    End Sub
    ====================================================
    I get no errors, but I also get no results in J2 (I know the IF AAA = 0...
    conditions are satisfied). Can anyone see what I'm doing wrong?

    Thanks in advance.

    MARTY

  2. #2
    Lonnie M.
    Guest

    Re: Problem with Running VBA code on Cell Change

    Hi, if the value of E7 changes then the target address as you called it
    will be equel to "$E$7". If you want "E7" to be returned use:
    Target.Address(False, False)

    You then need to test the target cell as to whether it exists within
    your required range.

    HTH--Lonnie


  3. #3
    ben
    Guest

    RE: Problem with Running VBA code on Cell Change

    It's your, range checking target address must be a single cell
    try
    ro1 = target.row
    co1 = target.column
    if co1>4 and co1<9 and ro1>6 and ro1 < 32 then
    instead of
    If Target.Address = "E7:H31" Then

    "Marty" wrote:

    > Hello:
    >
    > I'm using Excel 2003. I'm wanting to execute a series of code steps upon a
    > change to any cell within a specified range of cells. From some searching, I
    > found the following code example, which does what it says it's supposed to do:
    > =====================================================
    > 'This procedure will change the value of F10 to TRUE if the value in A1 is
    > changed
    > 'to a value greater than or equal to 10. Note that we change
    > 'Application.EnableEvents to False in order to prevent the Change event from
    > 'calling itself, and then restore the setting back to True.
    >
    > Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    > If Target.Address = "$A$1" Then
    > Application.EnableEvents = False
    > If Target.Value >= 10 Then
    > Range("F10").Value = True
    > Else
    > Range("F10").Value = False
    > End If
    > Application.EnableEvents = True
    > End If
    > End Sub
    > =====================================================
    > Seems simple enough. So I reasoned that I could delete the If-Else-End-If
    > block and replace it with my code that I want to execute, and change the
    > range from $A1$1 to the range of cells on which I want to activate my code if
    > it changes. In my case, this is "E7:H31"
    >
    > I can't get it to work. Basically, I want to put the new value of DIST into
    > cell J2 if any of AAA, BBB or CCC changes.
    >
    > Here is A SMALL PORTION of the code (NOTE: BBB, CCC and DDD are within the
    > range of cells that I want to monitor and execute upon changes. AAA and MDC
    > are only one of four fixed values and are outside the range. MDC is a
    > constant, also outside the range):
    > =====================================================
    > Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    > If Target.Address = "E7:H31" Then
    > Application.EnableEvents = False
    > Dim MYSHEET as Object
    > Set MYSHEET = Workbooks(ActiveWorkbook.Name).Sheets("Sheet1")
    >
    > If AAA = 0 And BBB = "M" And CCC = "3D" And DDD = "M" Then
    > DIST = {formula which contains AAA, BBB, CCC and DDD as variables}
    > If DIST < 0 Then
    > DIST = 0
    > End If
    > If DIST > MDC Then
    > DIST = MDC
    > End If
    > MYSHEET.Cells(2, 10) = DIST
    > End If
    >
    > Application.EnableEvents = True
    > End If
    > End Sub
    > ====================================================
    > I get no errors, but I also get no results in J2 (I know the IF AAA = 0...
    > conditions are satisfied). Can anyone see what I'm doing wrong?
    >
    > Thanks in advance.
    >
    > MARTY


  4. #4
    Lonnie M.
    Guest

    Re: Problem with Running VBA code on Cell Change

    Hi, me again. I would think that something like this would work for
    you:

    Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    Dim myRow&, myCol&
    myRow = Target.Row
    myCol = Target.Column
    If myRow > 6 And myRow < 31 Then
    If myCol > 4 And myCol < 9 Then
    'your code here
    End if
    End if
    End Sub

    Good Luck--Lonnie M.


  5. #5
    Lonnie M.
    Guest

    Re: Problem with Running VBA code on Cell Change

    Oops, good call Ben, the column should be less than 32.


  6. #6
    Lonnie M.
    Guest

    Re: Problem with Running VBA code on Cell Change

    Oops, good call Ben, the Row should be less than 32.


  7. #7
    Marty
    Guest

    Re: Problem with Running VBA code on Cell Change

    Thanks for the responses. I'll give it a go.

    MARTY

    "Lonnie M." wrote:

    > Hi, me again. I would think that something like this would work for
    > you:
    >
    > Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    > Dim myRow&, myCol&
    > myRow = Target.Row
    > myCol = Target.Column
    > If myRow > 6 And myRow < 31 Then
    > If myCol > 4 And myCol < 9 Then
    > 'your code here
    > End if
    > End if
    > End Sub
    >
    > Good Luck--Lonnie M.
    >
    >


  8. #8
    Dave Peterson
    Guest

    Re: Problem with Running VBA code on Cell Change

    I'm not sure if this'll work for you, but I like this kind of thing:

    if target.cells.count > 1 then exit sub
    if intersect(target,me.range("e7:h31")) is nothing then exit sub

    (one cell at a time and within e7:h31)



    Marty wrote:
    >
    > Hello:
    >
    > I'm using Excel 2003. I'm wanting to execute a series of code steps upon a
    > change to any cell within a specified range of cells. From some searching, I
    > found the following code example, which does what it says it's supposed to do:
    > =====================================================
    > 'This procedure will change the value of F10 to TRUE if the value in A1 is
    > changed
    > 'to a value greater than or equal to 10. Note that we change
    > 'Application.EnableEvents to False in order to prevent the Change event from
    > 'calling itself, and then restore the setting back to True.
    >
    > Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    > If Target.Address = "$A$1" Then
    > Application.EnableEvents = False
    > If Target.Value >= 10 Then
    > Range("F10").Value = True
    > Else
    > Range("F10").Value = False
    > End If
    > Application.EnableEvents = True
    > End If
    > End Sub
    > =====================================================
    > Seems simple enough. So I reasoned that I could delete the If-Else-End-If
    > block and replace it with my code that I want to execute, and change the
    > range from $A1$1 to the range of cells on which I want to activate my code if
    > it changes. In my case, this is "E7:H31"
    >
    > I can't get it to work. Basically, I want to put the new value of DIST into
    > cell J2 if any of AAA, BBB or CCC changes.
    >
    > Here is A SMALL PORTION of the code (NOTE: BBB, CCC and DDD are within the
    > range of cells that I want to monitor and execute upon changes. AAA and MDC
    > are only one of four fixed values and are outside the range. MDC is a
    > constant, also outside the range):
    > =====================================================
    > Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    > If Target.Address = "E7:H31" Then
    > Application.EnableEvents = False
    > Dim MYSHEET as Object
    > Set MYSHEET = Workbooks(ActiveWorkbook.Name).Sheets("Sheet1")
    >
    > If AAA = 0 And BBB = "M" And CCC = "3D" And DDD = "M" Then
    > DIST = {formula which contains AAA, BBB, CCC and DDD as variables}
    > If DIST < 0 Then
    > DIST = 0
    > End If
    > If DIST > MDC Then
    > DIST = MDC
    > End If
    > MYSHEET.Cells(2, 10) = DIST
    > End If
    >
    > Application.EnableEvents = True
    > End If
    > End Sub
    > ====================================================
    > I get no errors, but I also get no results in J2 (I know the IF AAA = 0...
    > conditions are satisfied). Can anyone see what I'm doing wrong?
    >
    > Thanks in advance.
    >
    > MARTY


    --

    Dave Peterson

  9. #9
    gocush
    Guest

    RE: Problem with Running VBA code on Cell Change

    Marty,
    I'm not following the part about AAA, BBB..... but this should get you
    started:


    In the ThisWorkbook module:

    Private Sub Worksheet_Change(ByVal Target as Range)

    ' Eliminate cases of changing more than one cell at a time
    If Target.Count>1 then Exit Sub

    'Make sure the change is to any cell in MyRange: E7:H31
    If Not Intersect(Target,Range("E7:H31") Is Nothing Then
    'Prevent this event from triggering itself over and over in a loop
    Application.EnableEvents=False

    Call YourMacro 'which is in a Standard Module
    here is where the Range("J2")=DIST variable
    comes in
    End If

    'Turn Events back on--this is not automatic like screen updating is
    Application.EnableEvents=True
    End Sub


    "Marty" wrote:

    > Hello:
    >
    > I'm using Excel 2003. I'm wanting to execute a series of code steps upon a
    > change to any cell within a specified range of cells. From some searching, I
    > found the following code example, which does what it says it's supposed to do:
    > =====================================================
    > 'This procedure will change the value of F10 to TRUE if the value in A1 is
    > changed
    > 'to a value greater than or equal to 10. Note that we change
    > 'Application.EnableEvents to False in order to prevent the Change event from
    > 'calling itself, and then restore the setting back to True.
    >
    > Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    > If Target.Address = "$A$1" Then
    > Application.EnableEvents = False
    > If Target.Value >= 10 Then
    > Range("F10").Value = True
    > Else
    > Range("F10").Value = False
    > End If
    > Application.EnableEvents = True
    > End If
    > End Sub
    > =====================================================
    > Seems simple enough. So I reasoned that I could delete the If-Else-End-If
    > block and replace it with my code that I want to execute, and change the
    > range from $A1$1 to the range of cells on which I want to activate my code if
    > it changes. In my case, this is "E7:H31"
    >
    > I can't get it to work. Basically, I want to put the new value of DIST into
    > cell J2 if any of AAA, BBB or CCC changes.
    >
    > Here is A SMALL PORTION of the code (NOTE: BBB, CCC and DDD are within the
    > range of cells that I want to monitor and execute upon changes. AAA and MDC
    > are only one of four fixed values and are outside the range. MDC is a
    > constant, also outside the range):
    > =====================================================
    > Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    > If Target.Address = "E7:H31" Then
    > Application.EnableEvents = False
    > Dim MYSHEET as Object
    > Set MYSHEET = Workbooks(ActiveWorkbook.Name).Sheets("Sheet1")
    >
    > If AAA = 0 And BBB = "M" And CCC = "3D" And DDD = "M" Then
    > DIST = {formula which contains AAA, BBB, CCC and DDD as variables}
    > If DIST < 0 Then
    > DIST = 0
    > End If
    > If DIST > MDC Then
    > DIST = MDC
    > End If
    > MYSHEET.Cells(2, 10) = DIST
    > End If
    >
    > Application.EnableEvents = True
    > End If
    > End Sub
    > ====================================================
    > I get no errors, but I also get no results in J2 (I know the IF AAA = 0...
    > conditions are satisfied). Can anyone see what I'm doing wrong?
    >
    > Thanks in advance.
    >
    > MARTY


+ 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