Solved: Change Event?

[Grantx]

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.

[lucas]

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

[Bob Phillips]

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

[Grantx]

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.

[lucas]

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

[Grantx]

Xld,

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

Grant.

[Grantx]

Copied and entered as directed but nothing happening. Suggestions?

Grant.

[unmarkedhelicopter]

Did you adjust the range as Bob indicated ?

[Bob Phillips]

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

[lucas]

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

[Grantx]

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.

[unmarkedhelicopter]

So what was the problem ?

[Grantx]

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.