Solved: Change Event?

I have spent over four days trying to solve this. I am new to VBA, and this is frustrating.

On an Excel sheet, A1=10, for example. This value is constantly changing.

B1:B5 also contains constantly changing values.

When A1 changes, I would like to sum B1:B5 with the resulting figure entered automatically into B7.

Further, I would like to record B7 in a separate column, eg starting from C3.

How is this to be done, step by step?

Thank you in anticipation.

Grant.

Hi Grant,
Not enough info....can you post an example file with some comments so we can see what you are trying to do.....

[vba]

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "A1" '<== change to suit

On Error GoTo ws_exit
Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
Me.Range("B7").Value = Application.Sum(Me.Range("B1:B5"))
If Me.Range("C3").Value <> "" Then
Me.Range("C" & Me.Rows.Count).End(xlUp).Offset(1, 0).Value = Me.Range("B7").Value
Else
Me.Range("C3").Value = Me.Range("B7").Value
End If
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub
[/vba]

This is worksheet event code, which means that it needs to be
placed in the appropriate worksheet code module, not a standard
code module. To do this, right-click on the sheet tab, select
the View Code option from the menu, and paste the code in.

Steve,

As requested, please see attachment.

Xld,

I've just noticed your reply while entering this in the "Reply to Thread" box. Will reply in due course.

Grant.

That's nice Bob. I couldn't wrap my head around what he was trying to do.

Xld,

Thank you for the solution; entered as suggested (it can't be tested until tomorrow am GMT).

Grant.

Copied and entered as directed but nothing happening. Suggestions?

Grant.

Did you adjust the range as Bob indicated ?

That's odd, because I opened your spreadsheet, poked the code in without any changes, removed your comments, and it worked perfectly.

Grant,
did you follow Bob's instructions in post #3 and put the code in the module for the sheet?

Gentleman,

I opened the sheet (zip file above) and re-copied Xld's code from above. It worked.

Thank you, Xld.

I have other questions which will be posted under new headings. Maybe I'll see you there.

Grant.

So what was the problem ?

XLD,

Please refer to the attachment.

Sum (B7) stopped working and populating the column C ended. However, A1
and B1:B5 were constantly changing. Can this be rectified?

(Is it possible to "cut and paste" code/text in the Message box?)

Thank you for your help.