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.....
Bob Phillips
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 ?
Bob Phillips
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.
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.