two cell accumulator

[JCook423]

Hello, I came across this,

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Address(False, False) = "A1" Then
If IsNumeric(.Value) Then
Application.EnableEvents = False
Range("B1").Value = Range("B1").Value + .Value
Application.EnableEvents = True
End If
End If
End With
End Sub

which works fine by dropping it into the sheet coding. However what I’m
trying to do is alter this code so I can “Assign Macro…†it to a
button/object/autoshape that I can click to trigger the event instead of the
event trigger automatically as it does now.

 

[Matthew Herbert]

JCook423,

You could click a button that runs the macro off of a cell as defined by the
current selection, i.e. you'll have to change the Set Rng = Selection(1) to
what you want. (Alternatively, you could loop through every cell in the
selection). Also, is Range("B1").Value = Range("B1").Value + .Value what you
really want to do?

Best,

Matthew Herbert

Sub SomeName()
Dim Rng As Range
Set Rng = Selection(1)
With Rng
If .Address(False, False) = "A1" Then
If IsNumeric(.Value) Then
Application.EnableEvents = False
Range("B1").Value = Range("B1").Value + .Value
Application.EnableEvents = True
End If
End If
End With
End Sub

 

[Sam Wilson]

sub AddtoTotal()

if isnumeric(Range("A1")) then Range("B1") = Range("B1")+Range("A1")

end sub


Is the simplest way I can think of.