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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Recording Changes Made to Worksheet
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!!) |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Recording Changes Made to Worksheet
Works like a charm! Thanks so very very much - you are a lifesaver!!!
"bigwheel" wrote: 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!!) |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Recording Changes Made to Worksheet
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Recording Changes Made to Worksheet
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! "DM Unseen" wrote: 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Recording Changes Made to Worksheet
That would be correct. Plus there are a lot of limitations imposed in
sharing a workbook (which may or may not affect you). -- Regards, Tom Ogilvy "Paige" wrote in message ... 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! "DM Unseen" wrote: 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Recording Changes Made to Worksheet
You're welcome and thanks for the feedback
"Paige" wrote: Works like a charm! Thanks so very very much - you are a lifesaver!!! "bigwheel" wrote: 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!!) |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Recording Changes Made to Worksheet
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? -- BerbatovIsGod "bigwheel" wrote: You're welcome and thanks for the feedback "Paige" wrote: Works like a charm! Thanks so very very much - you are a lifesaver!!! "bigwheel" wrote: 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!!) |
Reply |
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 |