VBC Code

[Leanne]

Hi,

Sorry but this is probably a first grade question but I am so new to VBA
that I am still working out how to type in code!

All I want to do is ensure that when I have done the function I want that I
am taken to the next available cell.

Basically what I am trying to do (and probably the wrong way) is keep a
record of entries in a field that constantly changes. When something else is
changed it is updated but I want to keep a record of all the dates in that
field.

Please help

 

[Leanne]

Hi Gary,
Thanks, this sounds exactly what I want. I have given it a go and I have
the following problems.
1. I have changed the Range to what I need (C2:C300) but it does not record
changes to other cells.
2. It records a change when other cells are changed but records what is in
C2 only and not the cell that was changed.

I need to keep a record of changes to several cells and they may not all be
changed at the same time. Also as each row is for a different customer I
want to see the name of the customer whos information was changed.

Thanks again

 

[Gary''s Student]

We are making excellent progress!

Post the code as you have modified it and we will get it to work

 

[Leanne]

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 = ra.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
Application.EnableEvents = True
End Sub

Thanks! On just about everything else Excel I have no trouble - just not VBA.

We are making excellent progress!

Post the code as you have modified it and we will get it to work

 

[Gary''s Student]

The only change you need to make in your version is to replace the line:
v = ra.Value
with:
v=t.Value

Here is another version that goes a little further. Since we can change any
of a set of cells and want to record the history, this version records that
address of the cell being changed in column C of the "Visit History"
worksheet. This we we know which cell has benn changed, the new value, and
the date:

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

Of course, if you try the new version, you must delete the old version.

 

[Leanne]

Thank you so much, this works great.

The only change you need to make in your version is to replace the line:
v = ra.Value
with:
v=t.Value

Here is another version that goes a little further. Since we can change any
of a set of cells and want to record the history, this version records that
address of the cell being changed in column C of the "Visit History"
worksheet. This we we know which cell has benn changed, the new value, and
the date:

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

Of course, if you try the new version, you must delete the old version.

 

[Leanne]

Hi Gary''s Student, I hope you pick this up again.

I have one final question. Is there a way to use a lookup function (which
in code I know little about) to show what the Cell address refers to if I
have a list?

This would be the result I am looking for -
Visit Date Date Changed Cell Company
01/05/2008 17/04/2008 $C$3 Portsmouth ERF

Using a list such as this created either in the same sheet or another one
altogether-
$C$2 Marchwood ERF
$C$3 Portsmouth ERF
$C$4 Marchwood Treatment Plant

I have asked this question of someone else but unfortunatly I can not
understand the instructions so was hoping you could help me as you have done
before.
Thanks

 

[Gary''s Student]

If you have an entry and want to look up that entry in another table and pull
information from that table, then see:

http://www.cpearson.com/excel/TablesAndLookups.aspx