Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Recording Changes Made to Worksheet
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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I get the Stop Recording toolbar to show on the worksheet? | Excel Discussion (Misc queries) | |||
How do I get the Stop Recording toolbar to show on the worksheet? | Excel Discussion (Misc queries) | |||
Recording changes made & Concatenating | Excel Discussion (Misc queries) | |||
Recording a high price on a worksheet | Excel Worksheet Functions | |||
How do I make the Stop Recording bar pop up when recording macros | Excel Worksheet Functions |