Capture Worksheet Change Value
 

Hi,

I have a data set that looks something like:
A B
1 ID#1 Text 1
2 ID#2 Text 2
3 ID#3 Text 3

Column A is a sequential unique identifier (text and a number). Column B is
free text. These values are duplicated across several worksheets.

The user can change the text in any cell in column B at any time (column A
is locked) on one specific worksheet but I have no way of knowing which
particular entry is being changed.

I need to capture the new text, capture the unique ID# associated with the
changed text use VLookup (or similar) on the ID# to find the correct row on
each of the other sheets in turn and copy the changed text to the appropriate
cell (hope that makes sense!).

What I can't figure out is how to capture the new text and the ID#. I have
been trying to use a Worksheet Change Event but don't know how to tell the
code to record which specific cell is being modified so that I can capture
the appropriate values as variables.

Any help would be appreciated.

This is in Office '07 under Vista.

TIA

Dave

Capture Worksheet Change Value
 

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("B:B")) Is Nothing Then Exit Sub
v1 = Target.Value
v2 = Cells(Target.Row, "A").Value
MsgBox (v1 & Chr(10) & v2)
End Sub

This will display the new text and the ID
--
Gary''s Student - gsnu200824


"Risky Dave" wrote:

Hi,

I have a data set that looks something like:
A B
1 ID#1 Text 1
2 ID#2 Text 2
3 ID#3 Text 3

Column A is a sequential unique identifier (text and a number). Column B is
free text. These values are duplicated across several worksheets.

The user can change the text in any cell in column B at any time (column A
is locked) on one specific worksheet but I have no way of knowing which
particular entry is being changed.

I need to capture the new text, capture the unique ID# associated with the
changed text use VLookup (or similar) on the ID# to find the correct row on
each of the other sheets in turn and copy the changed text to the appropriate
cell (hope that makes sense!).

What I can't figure out is how to capture the new text and the ID#. I have
been trying to use a Worksheet Change Event but don't know how to tell the
code to record which specific cell is being modified so that I can capture
the appropriate values as variables.

Any help would be appreciated.

This is in Office '07 under Vista.

TIA

Dave