| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
"Last Changed By" Cell
Dear Experts,
I have a spreadheet that many people are inputing, and I would like to capture the name of the last person who updated a certain cell in the nearby cell (well, inputs in one column, changes tracking in the nearby column). I would like to do so through some VBA code, could you please help me? Many, many thanks in advance! Best regards, -- Valeria |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
"Last Changed By" Cell
Hi
put the following code in your worksheet module: Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Target, Me.Range("A1:A100")) Is Nothing Then Exit Sub End If If Target.Cells.Count 1 Then Exit Sub On Error GoTo errhandler Application.EnableEvents = False With Target .Offset(0, 1).Value = Application.UserName .Offset(0, 2).Value = Format(Date, "DD-MMM-YYYY") End With errhandler: Application.EnableEvents = True End Sub -- Regards Frank Kabel Frankfurt, Germany "Valeria" schrieb im Newsbeitrag ... Dear Experts, I have a spreadheet that many people are inputing, and I would like to capture the name of the last person who updated a certain cell in the nearby cell (well, inputs in one column, changes tracking in the nearby column). I would like to do so through some VBA code, could you please help me? Many, many thanks in advance! Best regards, -- Valeria |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
"Last Changed By" Cell
Hi Frank,
I have tried it but I must be doing something wrong, since nothing seems to happen... I have put your code in the worksheet module, and then I have tried to modify some cells in the range from A1 to A100, but as said, nothing has happened! Could you please help me again? Thank you! Best regards, Valeria "Frank Kabel" wrote: Hi put the following code in your worksheet module: Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Target, Me.Range("A1:A100")) Is Nothing Then Exit Sub End If If Target.Cells.Count 1 Then Exit Sub On Error GoTo errhandler Application.EnableEvents = False With Target .Offset(0, 1).Value = Application.UserName .Offset(0, 2).Value = Format(Date, "DD-MMM-YYYY") End With errhandler: Application.EnableEvents = True End Sub -- Regards Frank Kabel Frankfurt, Germany "Valeria" schrieb im Newsbeitrag ... Dear Experts, I have a spreadheet that many people are inputing, and I would like to capture the name of the last person who updated a certain cell in the nearby cell (well, inputs in one column, changes tracking in the nearby column). I would like to do so through some VBA code, could you please help me? Many, many thanks in advance! Best regards, -- Valeria |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
"Last Changed By" Cell
Hi
and you have put it in the worksheet module of this specific sheet?. If you like email me your test file and I'll have a look at it -- Regards Frank Kabel Frankfurt, Germany "Valeria" schrieb im Newsbeitrag ... Hi Frank, I have tried it but I must be doing something wrong, since nothing seems to happen... I have put your code in the worksheet module, and then I have tried to modify some cells in the range from A1 to A100, but as said, nothing has happened! Could you please help me again? Thank you! Best regards, Valeria "Frank Kabel" wrote: Hi put the following code in your worksheet module: Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Target, Me.Range("A1:A100")) Is Nothing Then Exit Sub End If If Target.Cells.Count 1 Then Exit Sub On Error GoTo errhandler Application.EnableEvents = False With Target .Offset(0, 1).Value = Application.UserName .Offset(0, 2).Value = Format(Date, "DD-MMM-YYYY") End With errhandler: Application.EnableEvents = True End Sub -- Regards Frank Kabel Frankfurt, Germany "Valeria" schrieb im Newsbeitrag ... Dear Experts, I have a spreadheet that many people are inputing, and I would like to capture the name of the last person who updated a certain cell in the nearby cell (well, inputs in one column, changes tracking in the nearby column). I would like to do so through some VBA code, could you please help me? Many, many thanks in advance! Best regards, -- Valeria |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
"Last Changed By" Cell
Hi Valeria,
You install by right clicking on the worksheet tab, view code, insert Frank's code. It is a worksheet event macro, you can read more about them at http://www.mvps.org/dmcritchie/excel/event.htm If you ran an Event macro and did not make it the error handler as in Frank's code or there isn't one, you may have to make sure you have events enabled. In the VBE first you can type this into the Intermediate Window (Ctrl+G) of the VBE first see if you had a problem Application.EnableEvents then if it shows False fix it, by typing this into the Intermediate Window Application.EnableEvents = True --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "Frank Kabel" wrote in message ... Hi and you have put it in the worksheet module of this specific sheet?. If you like email me your test file and I'll have a look at it -- Regards Frank Kabel Frankfurt, Germany "Valeria" schrieb im Newsbeitrag ... Hi Frank, I have tried it but I must be doing something wrong, since nothing seems to happen... I have put your code in the worksheet module, and then I have tried to modify some cells in the range from A1 to A100, but as said, nothing has happened! Could you please help me again? Thank you! Best regards, Valeria "Frank Kabel" wrote: Hi put the following code in your worksheet module: Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Target, Me.Range("A1:A100")) Is Nothing Then Exit Sub End If If Target.Cells.Count 1 Then Exit Sub On Error GoTo errhandler Application.EnableEvents = False With Target .Offset(0, 1).Value = Application.UserName .Offset(0, 2).Value = Format(Date, "DD-MMM-YYYY") End With errhandler: Application.EnableEvents = True End Sub -- Regards Frank Kabel Frankfurt, Germany "Valeria" schrieb im Newsbeitrag ... Dear Experts, I have a spreadheet that many people are inputing, and I would like to capture the name of the last person who updated a certain cell in the nearby cell (well, inputs in one column, changes tracking in the nearby column). I would like to do so through some VBA code, could you please help me? Many, many thanks in advance! Best regards, -- Valeria |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
"Last Changed By" Cell
Hi David
Valeria sent me her test file and the reason was that the code was in the workbook module :-)) -- Regards Frank Kabel Frankfurt, Germany "David McRitchie" schrieb im Newsbeitrag ... Hi Valeria, You install by right clicking on the worksheet tab, view code, insert Frank's code. It is a worksheet event macro, you can read more about them at http://www.mvps.org/dmcritchie/excel/event.htm If you ran an Event macro and did not make it the error handler as in Frank's code or there isn't one, you may have to make sure you have events enabled. In the VBE first you can type this into the Intermediate Window (Ctrl+G) of the VBE first see if you had a problem Application.EnableEvents then if it shows False fix it, by typing this into the Intermediate Window Application.EnableEvents = True --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "Frank Kabel" wrote in message ... Hi and you have put it in the worksheet module of this specific sheet?. If you like email me your test file and I'll have a look at it -- Regards Frank Kabel Frankfurt, Germany "Valeria" schrieb im Newsbeitrag ... Hi Frank, I have tried it but I must be doing something wrong, since nothing seems to happen... I have put your code in the worksheet module, and then I have tried to modify some cells in the range from A1 to A100, but as said, nothing has happened! Could you please help me again? Thank you! Best regards, Valeria "Frank Kabel" wrote: Hi put the following code in your worksheet module: Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Target, Me.Range("A1:A100")) Is Nothing Then Exit Sub End If If Target.Cells.Count 1 Then Exit Sub On Error GoTo errhandler Application.EnableEvents = False With Target .Offset(0, 1).Value = Application.UserName .Offset(0, 2).Value = Format(Date, "DD-MMM-YYYY") End With errhandler: Application.EnableEvents = True End Sub -- Regards Frank Kabel Frankfurt, Germany "Valeria" schrieb im Newsbeitrag ... Dear Experts, I have a spreadheet that many people are inputing, and I would like to capture the name of the last person who updated a certain cell in the nearby cell (well, inputs in one column, changes tracking in the nearby column). I would like to do so through some VBA code, could you please help me? Many, many thanks in advance! Best regards, -- Valeria |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
"Last Changed By" Cell
Hi Frank,
Thanks for the feedback, didn't realize my reply was 8 hours later. -- David McRitchie, Microsoft MVP - Excel "Frank Kabel" wrote ... Hi David Valeria sent me her test file and the reason was that the code was in the workbook module :-)) |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Can the Excel cell fill color be changed using "IF" statements? | Excel Worksheet Functions | |||
| Excel - Golf - how to display "-2" as "2 Under" or "4"as "+4" or "4 Over" in a calculation cell | Excel Discussion (Misc queries) | |||
| "CELL("FILENAME") NOT UPDATE AFTER "SAVE AS" ACTION | Excel Discussion (Misc queries) | |||
| Help!!! Enter "7" in a cell and Excel changes the "7" to "11" immediately!!! | Excel Discussion (Misc queries) | |||
| Complex if test program possible? If "value" "value", paste "value" in another cell? | Excel Discussion (Misc queries) | |||
Linear Mode
