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

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

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 co14 and co1<9 and ro16 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

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.

Problem with Running VBA code on Cell Change
 

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

Problem with Running VBA code on Cell Change
 

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

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.

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

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.Count1 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