Microsoft Office Forums ** Using Functions

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 01-31-2011, 11:57 AM
djreyrey djreyrey is offline ** Using Functions Windows 7 ** Using Functions Office 2007
Novice
** Using Functions
 
Join Date: Mar 2010
Posts: 21
djreyrey is on a distinguished road
Exclamation ** 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
The functions are called by using the following formulas (respectively):

Quote:
=IF(C199>0, GetUserName(), "")
Quote:
=IF(C199>0, DateAndTime(), "")
I really appreciate your time!!

Thank you,

Rey
Reply With Quote
  #2  
Old 01-31-2011, 01:57 PM
Colin Legg's Avatar
Colin Legg Colin Legg is offline ** Using Functions Windows 7 32bit ** Using Functions Office 2010 32bit
Expert
 
Join Date: Jan 2011
Location: UK
Posts: 369
Colin Legg will become famous soon enough
Default

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?
Reply With Quote
  #3  
Old 01-31-2011, 02:08 PM
djreyrey djreyrey is offline ** Using Functions Windows 7 ** Using Functions Office 2007
Novice
** Using Functions
 
Join Date: Mar 2010
Posts: 21
djreyrey is on a distinguished road
Default

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
Reply With Quote
  #4  
Old 01-31-2011, 02:23 PM
Colin Legg's Avatar
Colin Legg Colin Legg is offline ** Using Functions Windows 7 32bit ** Using Functions Office 2010 32bit
Expert
 
Join Date: Jan 2011
Location: UK
Posts: 369
Colin Legg will become famous soon enough
Default

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?
Reply With Quote
  #5  
Old 01-31-2011, 02:28 PM
djreyrey djreyrey is offline ** Using Functions Windows 7 ** Using Functions Office 2007
Novice
** Using Functions
 
Join Date: Mar 2010
Posts: 21
djreyrey is on a distinguished road
Default

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
Reply With Quote
  #6  
Old 01-31-2011, 03:38 PM
Colin Legg's Avatar
Colin Legg Colin Legg is offline ** Using Functions Windows 7 32bit ** Using Functions Office 2010 32bit
Expert
 
Join Date: Jan 2011
Location: UK
Posts: 369
Colin Legg will become famous soon enough
Default

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
Hope that helps...
Attached Files
File Type: zip Example.zip (14.6 KB, 5 views)
Reply With Quote
  #7  
Old 01-31-2011, 03:51 PM
djreyrey djreyrey is offline ** Using Functions Windows 7 ** Using Functions Office 2007
Novice
** Using Functions
 
Join Date: Mar 2010
Posts: 21
djreyrey is on a distinguished road
Default

Colin,

Wow that worked great! Amazing! Thank you for your time and efforts.

Have a great week!

Rey
Reply With Quote
  #8  
Old 02-04-2011, 04:02 PM
djreyrey djreyrey is offline ** Using Functions Windows 7 ** Using Functions Office 2007
Novice
** Using Functions
 
Join Date: Mar 2010
Posts: 21
djreyrey is on a distinguished road
Default

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
Reply With Quote
  #9  
Old 02-05-2011, 01:32 AM
macropod's Avatar
macropod macropod is offline ** Using Functions Windows 7 32bit ** Using Functions Office 2000
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 19,653
macropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to behold
Default

Hi Rey,

You could change:
Code:
        Cells(lRow, 1).Value = GetUserName
        Cells(lRow, 2).Value = VBA.Now
to:
Code:
        If Cells(lRow, 1).Value <> "" Then
                Cells(lRow, 1).Value = GetUserName
                Cells(lRow, 2).Value = VBA.Now
        End If
__________________
Cheers,
Paul Edstein
[MS MVP - Word]
Reply With Quote
  #10  
Old 02-06-2011, 03:54 AM
Colin Legg's Avatar
Colin Legg Colin Legg is offline ** Using Functions Windows 7 32bit ** Using Functions Office 2010 32bit
Expert
 
Join Date: Jan 2011
Location: UK
Posts: 369
Colin Legg will become famous soon enough
Default

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
Hope that helps...
Reply With Quote
  #11  
Old 02-08-2011, 04:18 PM
djreyrey djreyrey is offline ** Using Functions Windows 7 ** Using Functions Office 2007
Novice
** Using Functions
 
Join Date: Mar 2010
Posts: 21
djreyrey is on a distinguished road
Default

Yes, this did help! Thank you both for sharing this with me. It works great.

Rey
Reply With Quote
Reply

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


All times are GMT -7. The time now is 03:19 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2019, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2019 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft