Or rather, this worked for me

s2.Cells(n, 3).Value = t.Offset(0, -2).Value

"Leanne" wrote:



"Leanne" wrote:

Sorry - New to the forum and did not think about the obvious.

This is the code for recording the change and own its own works well. It is
worksheet code. OFFICE_NOVICE - this sheet is the one I am trying to create
the form for also.

Private Sub Worksheet_Change(ByVal Target As Range)
Set t = Target
Set ra = Range("C2:C300")
Set s2 = Sheets("Visit History")
If Intersect(ra, t) Is Nothing Then Exit Sub
v = t.Value
n = s2.Cells(Rows.Count, 1).End(xlUp).Row + 1
Application.EnableEvents = False
s2.Cells(n, 1).Value = v
s2.Cells(n, 2).Value = Date
s2.Cells(n, 3).Value = t.Address
Application.EnableEvents = True
End Sub

The result I get is the following
Data changed When changed Cell address that was changed
08/01/2008 17/04/2008 $C$2

The sheet it takes the data from has the customer name in column A and dates
entered into column B & C.

Ideally what I want to see is the customer name either instead of the cell
address or beside it would be fine.

The following code is one that I have been given by someone else on this
site but I can not get it to work. I have created another sheet with the
cell reference and then the customer name as instructed but still can not get
it to work - it does not even bring back the data that was changed. I
thought I would add it however as it may help.

Private Sub Worksheet_Change(ByVal Target As Range)

'Select Range of target cells
Set ra = Range("C2:C300")
'select sheet where table is located
Set s2 = Sheets("Visit History")
'select sheet and Range of customer Table
Set Cust_Names = _
Sheets("Customer Names").Range("A1:A200") '*****Change *********

Application.EnableEvents = False

For Each cell In Target

If Intersect(ra, cell) Is Nothing Then Exit Sub

'get next empty row in table
NewRow = s2.Cells(Rows.Count, 1).End(xlUp).Row + 1

'Put target data into table
s2.Cells(NewRow, "A").Value = cell.Value
'Put date into Table
s2.Cells(NewRow, 2).Value = Date

'Get Customer Address
Set c = Cust_Names.Find(what:=cell, _
LookIn:=xlValues, lookat:=xlWhole)
If Not c Is Nothing Then
Cust_Addr = c.Offset(0, 1) 'change offset if
necessary
s2.Cells(NewRow, 3).Value = Cust_Addr
End If
Next cell

Application.EnableEvents = True
End Sub

Thanks again to anyone who can help.