Recording Changes Made to Worksheet

[Paige]

If someone changes an entry on my worksheet (Sheet1), then I want Excel to
enter (on hidden worksheet 'Sheet2' in the same file) the cell address that
was changed, the new value, the date, and the user name. So far I have the
following, which does what I want - almost. It goes to Sheet2, finds the
first blank cell down in Column A, enters the cell address that was changed,
goes to Column B and enters the new amount, goes to Column C and enters the
date, goes to Column D and enters the user's name (as provided via an input
box):

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Target, Range("$A$1:$b$400")) Is Nothing Then
Application.EnableEvents = False
Application.ScreenUpdating = False
With Worksheets("Sheet2")
.Select
.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).Select
ActiveCell.Value = Target.Address
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = Target.Value
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = Now()
ActiveCell.NumberFormat = "mm/dd/yy"
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = InputBox("You've made a change to the Rates tab.
Please enter your name here for historical purposes.")
Application.EnableEvents = True
Application.ScreenUpdating = True
End With
End If
End Sub

I have some questions re this:
1) The user is taken to the 'hidden' worksheet (Sheet2); I want them to
stay on the sheet that is being changed (i.e., Sheet1 in this instance).
I've tried changing to 'activate' versus 'select', but it still moves over to
Sheet2; how can I fix this?
2) If I can't fix it, then I know how to add something at the end to go
back to Sheet1, but if I do this, then I want to go back to the cell address
that the user just changed - how would I do that? (only needed if I can't fix
#1).
3) The input box asks for their name, but they can cancel out. How do I
force them to enter something in the box or have a box that doesn't have a
'cancel' option, or loop back until they DO enter something?
4) Is there a way to code it so that the user is asked for his name only
once; i.e., the first time that a cell is changed (in the specified range)
during the current 'session' and Excel copies the first input for any
remaining changes? If the user exits Sheet1 and goes to another sheet in the
same file (like Sheet3) or saves the file, and then later comes back to
Sheet1 and makes another change, then he should be prompted again for his
name via the input box, and the 'session' would start all over again.
5) Is there a way to capture what was in the target address PRIOR to it
being changed?

Hope I've explained this well enough. Any help on any of this would be
greatly appreciated.....thanks...Paige

 

[bigwheel]

1 & 2) Enter the instruction Worksheets("sheet1").Select before the line
Application.EnableEvents = True

3 & 4) Change the line to Activecell.Value = Application.Username

5) Can't think of a way because once excel has noticed that a change has
been made, it's already happened (but I'll keep thinking!!)

 

[Paige]

Works like a charm! Thanks so very very much - you are a lifesaver!!!

 

[DM Unseen]

Why isn't share workbook/track changes an option? This will let excel
do the tracking of all changes done by all users.

DM Unseen

 

[Paige]

I've never used that function; it appears tho that there is no option to
track changes on just one tab of the entire workbook, which is what I need to
do. Please advise if this is incorrect; thanks!

 

[Tom Ogilvy]

That would be correct. Plus there are a lot of limitations imposed in
sharing a workbook (which may or may not affect you).

 

[bigwheel]

You're welcome and thanks for the feedback

Works like a charm! Thanks so very very much - you are a lifesaver!!!

 

[PaxtonRoadEnd]

Not sure of the code, but can you not store the value of the cell in a public
variable at the time the cell is selected?