change event macro to add cells

  • Thread starter Thread starter fryguy
  • Start date Start date
F

fryguy

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.
 
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
 
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
 
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

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk
 
Another. Right click sheet tab>view code>insert 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
 
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
 
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")
Click to expand...

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
 
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
 
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 "
Click to expand...

Thanks everyone for your help

fryguy
 
Back
Top