#1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 22
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 22,906
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 22
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 22,906
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 22
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 22,906
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 22,906
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Separating date from a Date & Time stamp JT Excel Discussion (Misc queries) 9 June 10th 08 05:55 PM
Date Stamp Leon Excel Worksheet Functions 2 September 12th 07 02:07 PM
Create a button that will date stamp todays date in a cell Tom Meacham Excel Discussion (Misc queries) 3 January 11th 06 02:08 AM
Date stamp spreadsheet in excel to remind me of completion date Big fella Excel Worksheet Functions 1 October 18th 05 04:10 PM
date stamp Chris Excel Discussion (Misc queries) 2 May 10th 05 04:15 PM


All times are GMT +1. The time now is 01:20 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"