Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem with Running VBA code on Cell Change
Oops, good call Ben, the column should be less than 32.
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem with Running VBA code on Cell Change
Oops, good call Ben, the Row should be less than 32.
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Running a macro on cell value change | Excel Discussion (Misc queries) | |||
Running a macro on cell value change | Excel Discussion (Misc queries) | |||
Running a macro on cell value change | Excel Discussion (Misc queries) | |||
Running code on a drop down selection change | Excel Worksheet Functions | |||
Check cell for data before running code | Excel Programming |