#1
|
|||
|
|||
** Using Functions
Hello all! Thank you for stopping by.
I'm using the following functions in Excel 07 to retrieve the date and user name of the person who entered a new row. However, when I open the spreadsheet, the existing rows with user names update to my user name. Also, instead of the date showing when the row was entered, it updates to today's date. I don't want the information to change everytime I open the spreadsheet. It should reflect the user name who entered the new row. Here are the functions I'm using: Code:
Declare Function apiGetUserName Lib "advapi32.dll" Alias _ "GetUserNameA" (ByVal lpBuffer As String, _ nSize As Long) As Long Function GetUserName() As String Application.Volatile Dim sBuff As String * 25 Dim lBuffLen As Long lBuffLen = 25 apiGetUserName sBuff, lBuffLen GetUserName = Left(sBuff, lBuffLen - 1) End Function Function DateAndTime() DateAndTime = Now End Function Quote:
Quote:
Thank you, Rey |
#2
|
||||
|
||||
Hi Rey,
Instead of using formulas, you will need to update the relevant cells by using the worksheet's Change event handler. Let us know if you need further direction on how to do this? |
#3
|
|||
|
|||
Yes, please, can you assist me on how to use Excels Change event hander? I really appreciate it.
Do I need to convert the code? Rey |
#4
|
||||
|
||||
When the user updates a cell in column C, which corresponding cells should be updated with the username and time? Does this apply to the whole of column C?
|
#5
|
|||
|
|||
Hi Colin,
Column A contains the username, Column B contains the date, and Column C and beyond have user input data. This applies for the entire column. The spreadsheet is used as a request form. Each new row that is entered is a new request. The problem is users forget to enter their name and date so I figured I should automate this. Thanks again! Rey |
#6
|
||||
|
||||
This code goes in the sheet's class module as shown in the attached example.
Code:
Private Declare Function apiGetUserName Lib "advapi32.dll" Alias _ "GetUserNameA" (ByVal lpBuffer As String, _ nSize As Long) As Long Private Function GetUserName() As String Dim sBuff As String * 25 Dim lBuffLen As Long lBuffLen = 25 apiGetUserName sBuff, lBuffLen GetUserName = Left(sBuff, lBuffLen - 1) End Function Private Sub Worksheet_Change(ByVal Target As Range) Dim lRow As Long On Error GoTo ErrorHandler 'did the user change something in column C? If Not Intersect(Range("C:C"), Target) Is Nothing Then lRow = Target.Cells(1).Row Application.EnableEvents = False Cells(lRow, 1).Value = GetUserName Cells(lRow, 2).Value = VBA.Now End If ErrorExit: Application.EnableEvents = True Exit Sub ErrorHandler: Resume ErrorExit End Sub |
#7
|
|||
|
|||
Colin,
Wow that worked great! Amazing! Thank you for your time and efforts. Have a great week! Rey |
#8
|
|||
|
|||
Hi Colin,
I have another question regarding this function - sometimes the same row will get updated more than once. How can I make this function work so that it'll only grab the user name and date when the row was initially created and not any other time when the row is updated? I'm only interested in knowing who created the row and not who updated it. I really appreciate your time and knowledge. Rey |
#9
|
||||
|
||||
Hi Rey,
You could change: Code:
Cells(lRow, 1).Value = GetUserName Cells(lRow, 2).Value = VBA.Now Code:
If Cells(lRow, 1).Value <> "" Then Cells(lRow, 1).Value = GetUserName Cells(lRow, 2).Value = VBA.Now End If
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#10
|
||||
|
||||
Hi Rey,
Similar to Paul's reply, I would do it as shown below. This also assumes that if the user cleared out an entry in column C, the corresponding username and time stamp should also be cleared. Code:
Private Sub Worksheet_Change(ByVal Target As Range) Dim lRow As Long On Error GoTo ErrorHandler 'did the user change something in column C? If Not Intersect(Range("C:C"), Target) Is Nothing Then lRow = Target.Cells(1).Row Application.EnableEvents = False 'did the user clear the cell in column C? If IsEmpty(Target.Cells(1)) Then Range(Cells(lRow, 1), Cells(lRow, 2)).ClearContents 'only add info if column A is empty ElseIf IsEmpty(Cells(lRow, 1)) Then Cells(lRow, 1).Value = GetUserName Cells(lRow, 2).Value = VBA.Now End If End If ErrorExit: Application.EnableEvents = True Exit Sub ErrorHandler: Resume ErrorExit End Sub |
#11
|
|||
|
|||
Yes, this did help! Thank you both for sharing this with me. It works great.
Rey |
Thread Tools | |
Display Modes | |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Drop Down List and Functions (Linking) | sten | Excel | 0 | 03-17-2010 03:03 PM |
Auto-updating basic math functions & cross-referencing tables | FranklinBluth | Word Tables | 13 | 11-19-2009 10:26 AM |
Automated Functions?? | nickypatterson | Outlook | 0 | 08-27-2009 01:50 PM |
Merging a Word doc with VB functions contained | adamwbrown | Word | 0 | 08-13-2008 06:10 AM |
How to show help for addin functions? | Movses Asatryan | Excel | 0 | 03-02-2006 05:29 AM |