Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
change event macro to add cells
how can i make an event macro to update the value in C3 when a new value is
entered in B3 without the need to click an update button. thanks, fryguy. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
change event macro to add cells
Use a formula such as =C3=B3. When a new value is entered in B3, Excel will
automatically compute a new value in C3. In this case, C3 will be equal to B3. Tyro "fryguy" wrote in message ... how can i make an event macro to update the value in C3 when a new value is entered in B3 without the need to click an update button. thanks, fryguy. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
change event macro to add cells
Let's build an accumulator. From a clean sheet if we enter 5 in B3, then 5
will appear in C3. If we next enter 12 in B3, then 17 will appear in C3...etc. Private Sub Worksheet_Change(ByVal Target As Range) Set rb = Range("B3") Set rc = Range("C3") If Intersect(Target, rb) Is Nothing Then Exit Sub Application.EnableEvents = False rc.Value = rc.Value + rb.Value Application.EnableEvents = True End Sub -- Gary''s Student - gsnu200765 "fryguy" wrote: how can i make an event macro to update the value in C3 when a new value is entered in B3 without the need to click an update button. thanks, fryguy. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
change event macro to add cells
Where exactly are you suggesting the the OP puts this formula? If it is a
Worksheet formula in B3 or C3 it will result in a circular reference. With iteration checked it results in TRUE or FALSE as it does if placed in any other cell. Or did you mean that it should be in an event macro like the OP asked? -- Regards, Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "Tyro" wrote in message et... Use a formula such as =C3=B3. When a new value is entered in B3, Excel will automatically compute a new value in C3. In this case, C3 will be equal to B3. Tyro "fryguy" wrote in message ... how can i make an event macro to update the value in C3 when a new value is entered in B3 without the need to click an update button. thanks, fryguy. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
change event macro to add cells
Another. Right click sheet tabview codeinsert this
Private Sub Worksheet_Change(ByVal target As Excel.Range) If target.Address < "$B$3" Then exit sub Application.EnableEvents = False [c3] = target.Value + [c3] Application.EnableEvents = True End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "fryguy" wrote in message ... how can i make an event macro to update the value in C3 when a new value is entered in B3 without the need to click an update button. thanks, fryguy. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
change event macro to add cells
You have a couple of answers with event code.
Just take note: you will have no "paper trail" for checking in case of error in data entry. Gord Dibben MS Excel MVP On Fri, 11 Jan 2008 19:14:00 -0800, fryguy wrote: how can i make an event macro to update the value in C3 when a new value is entered in B3 without the need to click an update button. thanks, fryguy. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
change event macro to add cells
Thank you GS this one looks great, and easier to understand, but how would I
be able to expand (I should have asked the first time) the range to include a verticle range as the rb and rc values? I tried changing the Set rb = Range("B3") to Set rb = Range("B:B") Set rc = Range("C3") to Set rc = Range("C:C") and tried defining names for B3:B30 and same for C3:C30 and plugging the names in but nothing. Would I have to make it an array!? Arrays mess with my head. Thanks for any help you can provide. fryguy "Gary''s Student" wrote: Let's build an accumulator. From a clean sheet if we enter 5 in B3, then 5 will appear in C3. If we next enter 12 in B3, then 17 will appear in C3...etc. Private Sub Worksheet_Change(ByVal Target As Range) Set rb = Range("B3") Set rc = Range("C3") If Intersect(Target, rb) Is Nothing Then Exit Sub Application.EnableEvents = False rc.Value = rc.Value + rb.Value Application.EnableEvents = True End Sub -- Gary''s Student - gsnu200765 "fryguy" wrote: how can i make an event macro to update the value in C3 when a new value is entered in B3 without the need to click an update button. thanks, fryguy. |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
change event macro to add cells
Maybe this does what you want.
Makes each cell in C1:C30 an accumulator for cells in B1:B30 Private Sub Worksheet_Change(ByVal Target As Range) Const WS_RANGE As String = "B1:B30" Dim cell As Range On Error GoTo ws_exit: Application.EnableEvents = False If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then For Each cell In Target If Target.Value < "" Then With Target.Offset(0, 1) .Value = Target.Value + .Value End With End If Next cell End If ws_exit: Application.EnableEvents = True End Sub Or do you want just one cell............C3 to be an accumulator for B1:B30? With Range("C3") instead of With Target.Offset(0, 1) Gord Dibben MS Excel MVP On Sat, 12 Jan 2008 12:56:01 -0800, fryguy wrote: Thank you GS this one looks great, and easier to understand, but how would I be able to expand (I should have asked the first time) the range to include a verticle range as the rb and rc values? I tried changing the Set rb = Range("B3") to Set rb = Range("B:B") Set rc = Range("C3") to Set rc = Range("C:C") and tried defining names for B3:B30 and same for C3:C30 and plugging the names in but nothing. Would I have to make it an array!? Arrays mess with my head. Thanks for any help you can provide. fryguy "Gary''s Student" wrote: Let's build an accumulator. From a clean sheet if we enter 5 in B3, then 5 will appear in C3. If we next enter 12 in B3, then 17 will appear in C3...etc. Private Sub Worksheet_Change(ByVal Target As Range) Set rb = Range("B3") Set rc = Range("C3") If Intersect(Target, rb) Is Nothing Then Exit Sub Application.EnableEvents = False rc.Value = rc.Value + rb.Value Application.EnableEvents = True End Sub -- Gary''s Student - gsnu200765 "fryguy" wrote: how can i make an event macro to update the value in C3 when a new value is entered in B3 without the need to click an update button. thanks, fryguy. |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
change event macro to add cells
Thank you GD this works great and I even figured out hoe to copy it over
multiple times with: Const WS_RANGE As String = "B1:B30,d1:d30, etc " Thanks everyone for your help fryguy "Gord Dibben" wrote: Maybe this does what you want. Makes each cell in C1:C30 an accumulator for cells in B1:B30 Private Sub Worksheet_Change(ByVal Target As Range) Const WS_RANGE As String = "B1:B30" Dim cell As Range On Error GoTo ws_exit: Application.EnableEvents = False If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then For Each cell In Target If Target.Value < "" Then With Target.Offset(0, 1) .Value = Target.Value + .Value End With End If Next cell End If ws_exit: Application.EnableEvents = True End Sub Or do you want just one cell............C3 to be an accumulator for B1:B30? With Range("C3") instead of With Target.Offset(0, 1) Gord Dibben MS Excel MVP On Sat, 12 Jan 2008 12:56:01 -0800, fryguy wrote: Thank you GS this one looks great, and easier to understand, but how would I be able to expand (I should have asked the first time) the range to include a verticle range as the rb and rc values? I tried changing the Set rb = Range("B3") to Set rb = Range("B:B") Set rc = Range("C3") to Set rc = Range("C:C") and tried defining names for B3:B30 and same for C3:C30 and plugging the names in but nothing. Would I have to make it an array!? Arrays mess with my head. Thanks for any help you can provide. fryguy "Gary''s Student" wrote: Let's build an accumulator. From a clean sheet if we enter 5 in B3, then 5 will appear in C3. If we next enter 12 in B3, then 17 will appear in C3...etc. Private Sub Worksheet_Change(ByVal Target As Range) Set rb = Range("B3") Set rc = Range("C3") If Intersect(Target, rb) Is Nothing Then Exit Sub Application.EnableEvents = False rc.Value = rc.Value + rb.Value Application.EnableEvents = True End Sub -- Gary''s Student - gsnu200765 "fryguy" wrote: how can i make an event macro to update the value in C3 when a new value is entered in B3 without the need to click an update button. thanks, fryguy. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Referring to Ranges in Change-Event Macro? | Excel Discussion (Misc queries) | |||
Event Macro running another macro inside | Excel Discussion (Misc queries) | |||
Change event Macro | Excel Discussion (Misc queries) | |||
Change event? | Excel Discussion (Misc queries) | |||
Event Macro adjustment needed - need to change font color also | Excel Worksheet Functions |