When cell changes trigger macro 1

In excel...right click on sheet name then choose view code
then in the left drop down marked "general", change to "worksheet", then in right drop down choose the action to launch your code. a sub will appear...paste your code into the sub.

Hi,
The Worksheet Object has a number of Events. One event is the Worksheet_Change event. It executes whenever a value(s) is/are entered on the Worksheet.

1. Activate the VBE (alt+F11)

2. In the Project Explorer, double-click the Sheet on which you want to trigger the Change. This will activate the Code Window for that Worksheet

3. In the upper-right dropdown box (Object), select Worksheet

4. In the upper-left dropdown box (Procedure), select Change.

5. Target is the cell(s) changed. Target is a Range Object and can be manipulated like any other Range object.

BUT, if you want to know that the value that was entered is DIFFERENT than the value that was there previous to the Change event, you will have to compare the Worksheet_Change Target.Value with the Worksheet_SelectionChange Target.Value. That can be simple for a single cell or much more complex for multiple cell (like a paste that includes multiple cells)

hope this helps
Skip,
metzgsk@voughtaircraft.com

Thank you for replying,

This doesn't work!

I am new to VBA programming, what is wrong with this!

Function Worksheet_Change(ByVal Target As Range)

Target = Range("B2:F5&quot

MsgBox ("New Update&quot

End Function

Too vague....what are you trying to do with this code?

because the cell change when the dde link sends a new value, the function WorkSheet_Change doesn't work, I am using the calculate function instead. This works o.k except I need to be able to find what cell changed last becuase this function doesn't have a Target variable.

Is there any way I can find the range of the last update value.

HEY, Doesn't Work

Of course your code does not work! Your code is like saying...

Your code results in an endless loop.

What are you wanting to do? Skip,
metzgsk@voughtaircraft.com

Target.Address Skip,
metzgsk@voughtaircraft.com

Thanks for replying again!

I have a sheet receive frequent dde updates, when a value changes in a certain cell, I want to update a remote app using the new value of the cell.

The biggest problem is I have around 40 dde links, I want to pick up changes cell by cell. the Target variable doesn't seem to be avaliable in the Change function.

1. Cell Change (updated by DDE)
2. Trigger Module (using calculated function because of DDE links)
3. Pick up the cell Range of the changed cell and send out value of the recently changed cell.

Thanks for replying again!

I have a sheet receive frequent dde updates, when a value changes in a certain cell, I want to update a remote app using the new value of the cell.

The biggest problem is I have around 40 dde links, I want to pick up changes cell by cell. the Target variable doesn't seem to be avaliable in the Change function.

1. Cell Change (updated by DDE)
2. Trigger Module (using calculated function because of DDE links)
3. Pick up the cell Range of the changed cell and send out value of the recently changed cell.

The Worksheet Events seem only to work when that particular Worksheet is ACTIVE. Is your worksheet active when the dde update takes place? Skip,
metzgsk@voughtaircraft.com

The Excel sheet is left and open with active DDE links, the
active only picks up the currently selected cell.

Then I would suggest a timer loop that would check an array of is/was values.

Use the OnTime method...
OnTime Method (from VBA Help...)

Schedules a procedure to be run at a specified time in the future (either at a specific time of day or after a specific amount of time has passed).

Syntax

expression.OnTime(EarliestTime, Procedure, LatestTime, Schedule)

expression Required. An expression that returns an Application object.

EarliestTime Required Variant. The time when you want this procedure to be run.

Procedure Required String. The name of the procedure to be run.

LatestTime Optional Variant. The latest time at which the procedure can be run. For example, if LatestTime is set to EarliestTime + 30 and Microsoft Excel is not in Ready, Copy, Cut, or Find mode at EarliestTime because another procedure is running, Microsoft Excel will wait 30 seconds for the first procedure to complete. If Microsoft Excel is not in Ready mode within 30 seconds, the procedure won’t be run. If this argument is omitted, Microsoft Excel will wait until the procedure can be run.

Schedule Optional Variant. True to schedule a new OnTime procedure. False to clear a previously set procedure. The default value is True.

Remarks

Use Now + TimeValue(time) to schedule something to be run when a specific amount of time (counting from now) has elapsed. Use TimeValue(time) to schedule something to be run a specific time.
Skip,
metzgsk@voughtaircraft.com