| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Accumulator Logic
hi,
I'm using the following code taken from "McGimpsey & Associates" site and it works nicely for me but I'm not able to undrestand how the code works and what its logic is.can anybody explain to more about this codes,particulary the line "Application.EnableEvents = False or true"?thanx. Private Sub Worksheet_Change(ByVal Target As Excel.Range) With Target If .Address(False, False) = "D1" Then If IsNumeric(.Value) Then Application.EnableEvents = False Range("c1").Value = Range("c1").Value + .Value Application.EnableEvents = True End If Next End With End Sub |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Accumulator Logic
Looks like bad code to me :)
the next should be an end if Private Sub Worksheet_Change(ByVal Target As Excel.Range) With Target If .Address(False, False) = "D1" Then If IsNumeric(.Value) Then Application.EnableEvents = False Range("c1").Value = Range("c1").Value + .Value Application.EnableEvents = True End If End If End With End Sub i wouldn't know why enableevents would matter unless there is more code somewhere. -- -John Please rate when your question is answered to help us and others know what is helpful. "peyman" wrote: hi, I'm using the following code taken from "McGimpsey & Associates" site and it works nicely for me but I'm not able to undrestand how the code works and what its logic is.can anybody explain to more about this codes,particulary the line "Application.EnableEvents = False or true"?thanx. Private Sub Worksheet_Change(ByVal Target As Excel.Range) With Target If .Address(False, False) = "D1" Then If IsNumeric(.Value) Then Application.EnableEvents = False Range("c1").Value = Range("c1").Value + .Value Application.EnableEvents = True End If Next End With End Sub |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Accumulator Logic
I can probably help:
Private Sub Worksheet_Change(ByVal Target As Excel.Range) With Target If .Address(False, False) = "D1" Then We only operate on the accumulator if the changed range is the single cell D1. If IsNumeric(.Value) Then Checks that the entry is a number Application.EnableEvents = False Since the next line will change the value in C1, if we don't disable events, doing so will fire the Worksheet_Change code again. Disabling events allows us to change the C1 value without triggering the _Change event. Range("c1").Value = Range("c1").Value + .Value Adds the value in D1 to the value in C1 Application.EnableEvents = True Turns on Event macros again. End If Next End With End Sub In article , peyman wrote: hi, I'm using the following code taken from "McGimpsey & Associates" site and it works nicely for me but I'm not able to undrestand how the code works and what its logic is.can anybody explain to more about this codes,particulary the line "Application.EnableEvents = False or true"?thanx. Private Sub Worksheet_Change(ByVal Target As Excel.Range) With Target If .Address(False, False) = "D1" Then If IsNumeric(.Value) Then Application.EnableEvents = False Range("c1").Value = Range("c1").Value + .Value Application.EnableEvents = True End If Next End With End Sub |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Accumulator Logic
Just prevents firing the _Change macro again when the change is made to
C1. In article , John Bundy (remove) wrote: i wouldn't know why enableevents would matter unless there is more code somewhere. |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Accumulator Logic
Sorry John, because I extracted part of the codes.the "next" must be omitted
here. thank you. "John Bundy" wrote: Looks like bad code to me :) the next should be an end if Private Sub Worksheet_Change(ByVal Target As Excel.Range) With Target If .Address(False, False) = "D1" Then If IsNumeric(.Value) Then Application.EnableEvents = False Range("c1").Value = Range("c1").Value + .Value Application.EnableEvents = True End If End If End With End Sub i wouldn't know why enableevents would matter unless there is more code somewhere. -- -John Please rate when your question is answered to help us and others know what is helpful. "peyman" wrote: hi, I'm using the following code taken from "McGimpsey & Associates" site and it works nicely for me but I'm not able to undrestand how the code works and what its logic is.can anybody explain to more about this codes,particulary the line "Application.EnableEvents = False or true"?thanx. Private Sub Worksheet_Change(ByVal Target As Excel.Range) With Target If .Address(False, False) = "D1" Then If IsNumeric(.Value) Then Application.EnableEvents = False Range("c1").Value = Range("c1").Value + .Value Application.EnableEvents = True End If Next End With End Sub |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Accumulator Logic
Thanks a lot
"JE McGimpsey" wrote: I can probably help: Private Sub Worksheet_Change(ByVal Target As Excel.Range) With Target If .Address(False, False) = "D1" Then We only operate on the accumulator if the changed range is the single cell D1. If IsNumeric(.Value) Then Checks that the entry is a number Application.EnableEvents = False Since the next line will change the value in C1, if we don't disable events, doing so will fire the Worksheet_Change code again. Disabling events allows us to change the C1 value without triggering the _Change event. Range("c1").Value = Range("c1").Value + .Value Adds the value in D1 to the value in C1 Application.EnableEvents = True Turns on Event macros again. End If Next End With End Sub In article , peyman wrote: hi, I'm using the following code taken from "McGimpsey & Associates" site and it works nicely for me but I'm not able to undrestand how the code works and what its logic is.can anybody explain to more about this codes,particulary the line "Application.EnableEvents = False or true"?thanx. Private Sub Worksheet_Change(ByVal Target As Excel.Range) With Target If .Address(False, False) = "D1" Then If IsNumeric(.Value) Then Application.EnableEvents = False Range("c1").Value = Range("c1").Value + .Value Application.EnableEvents = True End If Next End With End Sub |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Accumulator Logic
Sorry,what do you mean by "fire the worksheet_chnage"?
"JE McGimpsey" wrote: I can probably help: Private Sub Worksheet_Change(ByVal Target As Excel.Range) With Target If .Address(False, False) = "D1" Then We only operate on the accumulator if the changed range is the single cell D1. If IsNumeric(.Value) Then Checks that the entry is a number Application.EnableEvents = False Since the next line will change the value in C1, if we don't disable events, doing so will fire the Worksheet_Change code again. Disabling events allows us to change the C1 value without triggering the _Change event. Range("c1").Value = Range("c1").Value + .Value Adds the value in D1 to the value in C1 Application.EnableEvents = True Turns on Event macros again. End If Next End With End Sub In article , peyman wrote: hi, I'm using the following code taken from "McGimpsey & Associates" site and it works nicely for me but I'm not able to undrestand how the code works and what its logic is.can anybody explain to more about this codes,particulary the line "Application.EnableEvents = False or true"?thanx. Private Sub Worksheet_Change(ByVal Target As Excel.Range) With Target If .Address(False, False) = "D1" Then If IsNumeric(.Value) Then Application.EnableEvents = False Range("c1").Value = Range("c1").Value + .Value Application.EnableEvents = True End If Next End With End Sub |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Accumulator Logic
Cause excel to execute the Worksheet_Change macro.
If a worksheet cell is changed, even if it's by the Worksheet_Change() macro, the macro will be put on hold, and the Worksheet_Change macro called with the new Target. In this case, the second call will exit without doing anything (since the new Target will be cell C1 rather than cell D1) before resuming execution of the first instance. But there's no reason for it to be executed at all. In article , peyman wrote: Sorry,what do you mean by "fire the worksheet_chnage"? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| accumulator with other than one | Excel Worksheet Functions | |||
| Date STOP Accumulator | Excel Discussion (Misc queries) | |||
| Need help with two cell accumulator | Excel Worksheet Functions | |||
| Two cell accumulator | Excel Worksheet Functions | |||
| Two cell accumulator | Excel Worksheet Functions | |||
Linear Mode
