Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Request macro code - when cell change event
I wish to request a macro code on this: 1. I have 3 cells A1 A2 and A3 2. Let's say that A1=5, A2=6, and A3=7 3. Here are the scenarios: a. When I change A1 from 5 to 10, A2 becomes, 11 and A3 becomes 12, meaning, that when A1 incremented by a value of 5, A2 and A3 automatically increments by 5 each. b. When I further chage A1, this time, from 10 to 14, then A2 becomes 15, and A3 becomes 16. c. But if I reset the value of A1 to zero or decrease its value to any number lower than its most previous value, A2 and A3 would not change. This means that A2 and A3 can only accumulate but not decrease when A1's value is decreased. Therefore, changing A1 from 14 to 10, A2 and A3 remains 15 and 16. Thank you for any assistance -- Rhey197 ----------------------------------------------------------------------- Rhey1971's Profile: http://www.excelforum.com/member.php...fo&userid=1233 View this thread: http://www.excelforum.com/showthread.php?threadid=53768 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Request macro code - when cell change event
Option Explicit
Const WS_RANGE As String = "A1" Private myCell '----------------------------------------------------------------- Private Sub Worksheet_Change(ByVal Target As Range) '----------------------------------------------------------------- On Error GoTo ws_exit: Application.EnableEvents = False If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then If Target.Value myCell Then Target.Offset(1, 0).Value = Target.Value + 1 Target.Offset(2, 0).Value = Target.Value + 2 End If End If ws_exit: Application.EnableEvents = True End Sub '----------------------------------------------------------------- Private Sub Worksheet_SelectionChange(ByVal Target As Range) '----------------------------------------------------------------- If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then myCell = Target.Value End If End Sub 'This is worksheet event code, which means that it needs to be 'placed in the appropriate worksheet code module, not a standard 'code module. To do this, right-click on the sheet tab, select 'the View Code option from the menu, and paste the code in. -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Rhey1971" wrote in message ... I wish to request a macro code on this: 1. I have 3 cells A1 A2 and A3 2. Let's say that A1=5, A2=6, and A3=7 3. Here are the scenarios: a. When I change A1 from 5 to 10, A2 becomes, 11 and A3 becomes 12, meaning, that when A1 incremented by a value of 5, A2 and A3 automatically increments by 5 each. b. When I further chage A1, this time, from 10 to 14, then A2 becomes 15, and A3 becomes 16. c. But if I reset the value of A1 to zero or decrease its value to any number lower than its most previous value, A2 and A3 would not change. This means that A2 and A3 can only accumulate but not decrease when A1's value is decreased. Therefore, changing A1 from 14 to 10, A2 and A3 remains 15 and 16. Thank you for any assistance. -- Rhey1971 ------------------------------------------------------------------------ Rhey1971's Profile: http://www.excelforum.com/member.php...o&userid=12337 View this thread: http://www.excelforum.com/showthread...hreadid=537685 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Request macro code - when cell change event
Paste the following in Worksheet code:
'---------- Private Sub Worksheet_Change(ByVal Target As Range) Static prevValue As Long Dim iDiff As Long If Intersect(Target, Range("A1")) Is Nothing _ Or Target.Count 1 Then Exit Sub If Target.Value prevValue Then iDiff = Target.Value - prevValue Range("A2").Value = Range("A2").Value + iDiff Range("A3").Value = Range("A3").Value + iDiff End If prevValue = Target.Value End Sub '---------- TH -- AP "Rhey1971" a écrit dans le message de ... I wish to request a macro code on this: 1. I have 3 cells A1 A2 and A3 2. Let's say that A1=5, A2=6, and A3=7 3. Here are the scenarios: a. When I change A1 from 5 to 10, A2 becomes, 11 and A3 becomes 12, meaning, that when A1 incremented by a value of 5, A2 and A3 automatically increments by 5 each. b. When I further chage A1, this time, from 10 to 14, then A2 becomes 15, and A3 becomes 16. c. But if I reset the value of A1 to zero or decrease its value to any number lower than its most previous value, A2 and A3 would not change. This means that A2 and A3 can only accumulate but not decrease when A1's value is decreased. Therefore, changing A1 from 14 to 10, A2 and A3 remains 15 and 16. Thank you for any assistance. -- Rhey1971 ------------------------------------------------------------------------ Rhey1971's Profile: http://www.excelforum.com/member.php...o&userid=12337 View this thread: http://www.excelforum.com/showthread...hreadid=537685 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Request macro code - when cell change event
You need several change event macros as you need to be able to track the value of a1 from the time the workbook is opend or the sheet is activated try these 3 copy and paste all on to the module for your sheet Option Explicit Dim iA1 As Integer Private Sub Worksheet_Activate() If Target.Address = "$A$1" Then iA1 = Range("a1").Value End If End Sub Private Sub Worksheet_Change(ByVal Target As Range) Dim iDif As Integer If Target.Address = "$A$1" Then If iA1 < Target.Value Then Application.EnableEvents = False iDif = Target.Value - iA1 Range("a2").Value = Range("a2").Value + iDif Range("a3").Value = Range("a3").Value + iDif End If iA1 = Target.Value End If Application.EnableEvents = True End Sub Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Address = "$A$1" Then iA1 = Range("a1").Value End If End Sub -- mudraker ------------------------------------------------------------------------ mudraker's Profile: http://www.excelforum.com/member.php...fo&userid=2473 View this thread: http://www.excelforum.com/showthread...hreadid=537685 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Request macro code - when cell change event
Oooops!
Correct code: '-------- Private Sub Worksheet_Change(ByVal Target As Range) Static prevValue As Long Dim iDiff As Long If Intersect(Target, Range("A1")) Is Nothing _ Or Target.Count 1 Then Exit Sub If Target.Value prevValue Then iDiff = Target.Value - prevValue Application.EnableEvents = False Range("A2").Value = Range("A2").Value + iDiff Range("A3").Value = Range("A3").Value + iDiff Application.EnableEvents = True End If prevValue = Target.Value End Sub '-------- "Ardus Petus" a écrit dans le message de ... Paste the following in Worksheet code: '---------- Private Sub Worksheet_Change(ByVal Target As Range) Static prevValue As Long Dim iDiff As Long If Intersect(Target, Range("A1")) Is Nothing _ Or Target.Count 1 Then Exit Sub If Target.Value prevValue Then iDiff = Target.Value - prevValue Range("A2").Value = Range("A2").Value + iDiff Range("A3").Value = Range("A3").Value + iDiff End If prevValue = Target.Value End Sub '---------- TH -- AP "Rhey1971" a écrit dans le message de ... I wish to request a macro code on this: 1. I have 3 cells A1 A2 and A3 2. Let's say that A1=5, A2=6, and A3=7 3. Here are the scenarios: a. When I change A1 from 5 to 10, A2 becomes, 11 and A3 becomes 12, meaning, that when A1 incremented by a value of 5, A2 and A3 automatically increments by 5 each. b. When I further chage A1, this time, from 10 to 14, then A2 becomes 15, and A3 becomes 16. c. But if I reset the value of A1 to zero or decrease its value to any number lower than its most previous value, A2 and A3 would not change. This means that A2 and A3 can only accumulate but not decrease when A1's value is decreased. Therefore, changing A1 from 14 to 10, A2 and A3 remains 15 and 16. Thank you for any assistance. -- Rhey1971 ------------------------------------------------------------------------ Rhey1971's Profile: http://www.excelforum.com/member.php...o&userid=12337 View this thread: http://www.excelforum.com/showthread...hreadid=537685 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Cell value change to trigger macro (worksheet change event?) | Excel Programming | |||
Cell value Change Event - Need to activate macro | Excel Programming | |||
Need code for simple change event | Excel Programming | |||
Copy Sheets minus Worksheet Change Event code & Macro Buttons | Excel Programming |