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.

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.

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.

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.

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.

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.

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.

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.

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.