Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Date stamp user
I would like to date stamp spreadsheet any time it is updated along with user
name of computer doing it cheers Stuart |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Date stamp user
Where would like to see this information?
This code placed into Thisworkbook module will put the info in a cell. Private Sub Workbook_BeforeSave(ByVal SaveAsUI _ As Boolean, Cancel As Boolean) Sheets("Sheet1").Range("A1").Value = Environ("Username") & " " _ & Format(ThisWorkbook.BuiltinDocumentProperties("Las t Save Time"), _ "yyyy-mmm-dd hh:mm:ss") End Sub Gord Dibben MS Excel MVP On Thu, 6 Aug 2009 06:42:08 -0700, Stuart WJG wrote: I would like to date stamp spreadsheet any time it is updated along with user name of computer doing it cheers Stuart |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Date stamp user
I have put the below in the worksheet but only get the date. I presume in the
sheet is named ENQUIRY i replace "sheet 1" below with "ENQUIRY" and change the range to whatever call i want the info Stuart "Gord Dibben" wrote: Where would like to see this information? This code placed into Thisworkbook module will put the info in a cell. Private Sub Workbook_BeforeSave(ByVal SaveAsUI _ As Boolean, Cancel As Boolean) Sheets("Sheet1").Range("A1").Value = Environ("Username") & " " _ & Format(ThisWorkbook.BuiltinDocumentProperties("Las t Save Time"), _ "yyyy-mmm-dd hh:mm:ss") End Sub Gord Dibben MS Excel MVP On Thu, 6 Aug 2009 06:42:08 -0700, Stuart WJG wrote: I would like to date stamp spreadsheet any time it is updated along with user name of computer doing it cheers Stuart |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Date stamp user
Yes.........change sheet1 to your sheet name and adjust the range.
Are you not seeing the username of the logged in user? A re-read of your original shows you want computer name, not username. To get computer name..........not logged-in user. Add these to a General module. Private Declare Function GetComputerName Lib "kernel32" _ Alias "GetComputerNameA" (ByVal lpBuffer As String, nSize As Long) _ As Long Public Function NameOfComputer() ' Returns the name of the computer Dim ComputerName As String Dim ComputerNameLen As Long Dim Result As Long ComputerNameLen = 256 ComputerName = Space(ComputerNameLen) Result = GetComputerName(ComputerName, ComputerNameLen) If Result < 0 Then NameOfComputer = Left(ComputerName, ComputerNameLen) Else NameOfComputer = "Unknown" End If End Function Then add to Thisworkbook module this new code. Editing sheet1 and range as you wish. Private Sub Workbook_BeforeSave(ByVal SaveAsUI _ As Boolean, Cancel As Boolean) Sheets("Sheet1").Range("A1").Value = NameOfComputer() & " " _ & Format(ThisWorkbook.BuiltinDocumentProperties("Las t Save Time"), _ "yyyy-mmm-dd hh:mm:ss") End Sub Gord On Thu, 6 Aug 2009 08:56:02 -0700, Stuart WJG wrote: I have put the below in the worksheet but only get the date. I presume in the sheet is named ENQUIRY i replace "sheet 1" below with "ENQUIRY" and change the range to whatever call i want the info Stuart "Gord Dibben" wrote: Where would like to see this information? This code placed into Thisworkbook module will put the info in a cell. Private Sub Workbook_BeforeSave(ByVal SaveAsUI _ As Boolean, Cancel As Boolean) Sheets("Sheet1").Range("A1").Value = Environ("Username") & " " _ & Format(ThisWorkbook.BuiltinDocumentProperties("Las t Save Time"), _ "yyyy-mmm-dd hh:mm:ss") End Sub Gord Dibben MS Excel MVP On Thu, 6 Aug 2009 06:42:08 -0700, Stuart WJG wrote: I would like to date stamp spreadsheet any time it is updated along with user name of computer doing it cheers Stuart |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Date stamp user
Hi Gord,
Can you please guide me step by step how to do this or point me to the right direction? Thanks Val "Gord Dibben" wrote: Yes.........change sheet1 to your sheet name and adjust the range. Are you not seeing the username of the logged in user? A re-read of your original shows you want computer name, not username. To get computer name..........not logged-in user. Add these to a General module. Private Declare Function GetComputerName Lib "kernel32" _ Alias "GetComputerNameA" (ByVal lpBuffer As String, nSize As Long) _ As Long Public Function NameOfComputer() ' Returns the name of the computer Dim ComputerName As String Dim ComputerNameLen As Long Dim Result As Long ComputerNameLen = 256 ComputerName = Space(ComputerNameLen) Result = GetComputerName(ComputerName, ComputerNameLen) If Result < 0 Then NameOfComputer = Left(ComputerName, ComputerNameLen) Else NameOfComputer = "Unknown" End If End Function Then add to Thisworkbook module this new code. Editing sheet1 and range as you wish. Private Sub Workbook_BeforeSave(ByVal SaveAsUI _ As Boolean, Cancel As Boolean) Sheets("Sheet1").Range("A1").Value = NameOfComputer() & " " _ & Format(ThisWorkbook.BuiltinDocumentProperties("Las t Save Time"), _ "yyyy-mmm-dd hh:mm:ss") End Sub Gord On Thu, 6 Aug 2009 08:56:02 -0700, Stuart WJG wrote: I have put the below in the worksheet but only get the date. I presume in the sheet is named ENQUIRY i replace "sheet 1" below with "ENQUIRY" and change the range to whatever call i want the info Stuart "Gord Dibben" wrote: Where would like to see this information? This code placed into Thisworkbook module will put the info in a cell. Private Sub Workbook_BeforeSave(ByVal SaveAsUI _ As Boolean, Cancel As Boolean) Sheets("Sheet1").Range("A1").Value = Environ("Username") & " " _ & Format(ThisWorkbook.BuiltinDocumentProperties("Las t Save Time"), _ "yyyy-mmm-dd hh:mm:ss") End Sub Gord Dibben MS Excel MVP On Thu, 6 Aug 2009 06:42:08 -0700, Stuart WJG wrote: I would like to date stamp spreadsheet any time it is updated along with user name of computer doing it cheers Stuart |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Date stamp user
Hi Gord
Still did not work. But this does,As I couls already to date stamp I added your bit after "environ Private Sub Worksheet_Change(ByVal Target As Range) Application.EnableEvents = False Range("H1").Value = Environ("Username") & "" & " " _ & Format(ThisWorkbook.BuiltinDocumentProperties("Las t Save Time"), _ "dd-mmm-yy hh:mm") Application.EnableEvents = True End Sub Many thanks Stuart "Gord Dibben" wrote: Yes.........change sheet1 to your sheet name and adjust the range. Are you not seeing the username of the logged in user? A re-read of your original shows you want computer name, not username. To get computer name..........not logged-in user. Add these to a General module. Private Declare Function GetComputerName Lib "kernel32" _ Alias "GetComputerNameA" (ByVal lpBuffer As String, nSize As Long) _ As Long Public Function NameOfComputer() ' Returns the name of the computer Dim ComputerName As String Dim ComputerNameLen As Long Dim Result As Long ComputerNameLen = 256 ComputerName = Space(ComputerNameLen) Result = GetComputerName(ComputerName, ComputerNameLen) If Result < 0 Then NameOfComputer = Left(ComputerName, ComputerNameLen) Else NameOfComputer = "Unknown" End If End Function Then add to Thisworkbook module this new code. Editing sheet1 and range as you wish. Private Sub Workbook_BeforeSave(ByVal SaveAsUI _ As Boolean, Cancel As Boolean) Sheets("Sheet1").Range("A1").Value = NameOfComputer() & " " _ & Format(ThisWorkbook.BuiltinDocumentProperties("Las t Save Time"), _ "yyyy-mmm-dd hh:mm:ss") End Sub Gord On Thu, 6 Aug 2009 08:56:02 -0700, Stuart WJG wrote: I have put the below in the worksheet but only get the date. I presume in the sheet is named ENQUIRY i replace "sheet 1" below with "ENQUIRY" and change the range to whatever call i want the info Stuart "Gord Dibben" wrote: Where would like to see this information? This code placed into Thisworkbook module will put the info in a cell. Private Sub Workbook_BeforeSave(ByVal SaveAsUI _ As Boolean, Cancel As Boolean) Sheets("Sheet1").Range("A1").Value = Environ("Username") & " " _ & Format(ThisWorkbook.BuiltinDocumentProperties("Las t Save Time"), _ "yyyy-mmm-dd hh:mm:ss") End Sub Gord Dibben MS Excel MVP On Thu, 6 Aug 2009 06:42:08 -0700, Stuart WJG wrote: I would like to date stamp spreadsheet any time it is updated along with user name of computer doing it cheers Stuart |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Date stamp user
I don't know why you have altered to a worksheet change event.
That will update every time any change is on Sheet1 made even if the workbook is closed without saving. Could lead to monitoring problems in my estimation, but if you're happy I'll leave you be. Gord On Fri, 7 Aug 2009 00:45:01 -0700, Stuart WJG wrote: Hi Gord Still did not work. But this does,As I couls already to date stamp I added your bit after "environ Private Sub Worksheet_Change(ByVal Target As Range) Application.EnableEvents = False Range("H1").Value = Environ("Username") & "" & " " _ & Format(ThisWorkbook.BuiltinDocumentProperties("Las t Save Time"), _ "dd-mmm-yy hh:mm") Application.EnableEvents = True End Sub Many thanks Stuart "Gord Dibben" wrote: Yes.........change sheet1 to your sheet name and adjust the range. Are you not seeing the username of the logged in user? A re-read of your original shows you want computer name, not username. To get computer name..........not logged-in user. Add these to a General module. Private Declare Function GetComputerName Lib "kernel32" _ Alias "GetComputerNameA" (ByVal lpBuffer As String, nSize As Long) _ As Long Public Function NameOfComputer() ' Returns the name of the computer Dim ComputerName As String Dim ComputerNameLen As Long Dim Result As Long ComputerNameLen = 256 ComputerName = Space(ComputerNameLen) Result = GetComputerName(ComputerName, ComputerNameLen) If Result < 0 Then NameOfComputer = Left(ComputerName, ComputerNameLen) Else NameOfComputer = "Unknown" End If End Function Then add to Thisworkbook module this new code. Editing sheet1 and range as you wish. Private Sub Workbook_BeforeSave(ByVal SaveAsUI _ As Boolean, Cancel As Boolean) Sheets("Sheet1").Range("A1").Value = NameOfComputer() & " " _ & Format(ThisWorkbook.BuiltinDocumentProperties("Las t Save Time"), _ "yyyy-mmm-dd hh:mm:ss") End Sub Gord On Thu, 6 Aug 2009 08:56:02 -0700, Stuart WJG wrote: I have put the below in the worksheet but only get the date. I presume in the sheet is named ENQUIRY i replace "sheet 1" below with "ENQUIRY" and change the range to whatever call i want the info Stuart "Gord Dibben" wrote: Where would like to see this information? This code placed into Thisworkbook module will put the info in a cell. Private Sub Workbook_BeforeSave(ByVal SaveAsUI _ As Boolean, Cancel As Boolean) Sheets("Sheet1").Range("A1").Value = Environ("Username") & " " _ & Format(ThisWorkbook.BuiltinDocumentProperties("Las t Save Time"), _ "yyyy-mmm-dd hh:mm:ss") End Sub Gord Dibben MS Excel MVP On Thu, 6 Aug 2009 06:42:08 -0700, Stuart WJG wrote: I would like to date stamp spreadsheet any time it is updated along with user name of computer doing it cheers Stuart |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Date stamp user
One other point with using worksheet code.............only that Sheet1 will
stamp when a user makes changes. Any other sheet and no stamp. If the Environ("Username") works in the sheet event code I can't see why you get no username when running the original Beforesave code from Thisworkbbok module. Gord On Fri, 7 Aug 2009 00:45:01 -0700, Stuart WJG wrote: Hi Gord Still did not work. But this does,As I couls already to date stamp I added your bit after "environ Private Sub Worksheet_Change(ByVal Target As Range) Application.EnableEvents = False Range("H1").Value = Environ("Username") & "" & " " _ & Format(ThisWorkbook.BuiltinDocumentProperties("Las t Save Time"), _ "dd-mmm-yy hh:mm") Application.EnableEvents = True End Sub Many thanks Stuart "Gord Dibben" wrote: Yes.........change sheet1 to your sheet name and adjust the range. Are you not seeing the username of the logged in user? A re-read of your original shows you want computer name, not username. To get computer name..........not logged-in user. Add these to a General module. Private Declare Function GetComputerName Lib "kernel32" _ Alias "GetComputerNameA" (ByVal lpBuffer As String, nSize As Long) _ As Long Public Function NameOfComputer() ' Returns the name of the computer Dim ComputerName As String Dim ComputerNameLen As Long Dim Result As Long ComputerNameLen = 256 ComputerName = Space(ComputerNameLen) Result = GetComputerName(ComputerName, ComputerNameLen) If Result < 0 Then NameOfComputer = Left(ComputerName, ComputerNameLen) Else NameOfComputer = "Unknown" End If End Function Then add to Thisworkbook module this new code. Editing sheet1 and range as you wish. Private Sub Workbook_BeforeSave(ByVal SaveAsUI _ As Boolean, Cancel As Boolean) Sheets("Sheet1").Range("A1").Value = NameOfComputer() & " " _ & Format(ThisWorkbook.BuiltinDocumentProperties("Las t Save Time"), _ "yyyy-mmm-dd hh:mm:ss") End Sub Gord On Thu, 6 Aug 2009 08:56:02 -0700, Stuart WJG wrote: I have put the below in the worksheet but only get the date. I presume in the sheet is named ENQUIRY i replace "sheet 1" below with "ENQUIRY" and change the range to whatever call i want the info Stuart "Gord Dibben" wrote: Where would like to see this information? This code placed into Thisworkbook module will put the info in a cell. Private Sub Workbook_BeforeSave(ByVal SaveAsUI _ As Boolean, Cancel As Boolean) Sheets("Sheet1").Range("A1").Value = Environ("Username") & " " _ & Format(ThisWorkbook.BuiltinDocumentProperties("Las t Save Time"), _ "yyyy-mmm-dd hh:mm:ss") End Sub Gord Dibben MS Excel MVP On Thu, 6 Aug 2009 06:42:08 -0700, Stuart WJG wrote: I would like to date stamp spreadsheet any time it is updated along with user name of computer doing it cheers Stuart |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Separating date from a Date & Time stamp | Excel Discussion (Misc queries) | |||
Date Stamp | Excel Worksheet Functions | |||
Create a button that will date stamp todays date in a cell | Excel Discussion (Misc queries) | |||
Date stamp spreadsheet in excel to remind me of completion date | Excel Worksheet Functions | |||
date stamp | Excel Discussion (Misc queries) |