PDA

View Full Version : Solved: Change Event?



Grantx
11-13-2007, 03:08 PM
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
11-13-2007, 04:15 PM
Hi Grant,
Not enough info....can you post an example file with some comments so we can see what you are trying to do.....

xld
11-13-2007, 04:38 PM
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


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
11-13-2007, 04:46 PM
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
11-13-2007, 05:06 PM
That's nice Bob. I couldn't wrap my head around what he was trying to do.

Grantx
11-13-2007, 05:44 PM
Xld,

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

Grant.

Grantx
11-14-2007, 04:00 AM
Copied and entered as directed but nothing happening. Suggestions?

Grant.

unmarkedhelicopter
11-14-2007, 04:34 AM
Did you adjust the range as Bob indicated ?

xld
11-14-2007, 05:31 AM
That's odd, because I opened your spreadsheet, poked the code in without any changes, removed your comments, and it worked perfectly.

lucas
11-14-2007, 06:58 AM
Grant,
did you follow Bob's instructions in post #3 and put the code in the module for the sheet?

Grantx
11-14-2007, 09:26 AM
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
11-14-2007, 07:31 PM
So what was the problem ?

Grantx
11-15-2007, 10:51 AM
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.