Results 1 to 9 of 9
  1. #1
    New Lounger
    Join Date
    Mar 2006
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    update date/time in a cell only when macro is run (11.8012.6568/2003)

    have an excel sheet that i would like the date and time updated in (currently have it set to = NOW()) only when the attached macro is run. is there a way to do this?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: update date/time in a cell only when macro is run (11.8012.6568/2003)

    Welcome to Woody's Lounge!

    What do you mean by "the date and time updated in"?
    What do you mean by "the attached macro"?

  3. #3
    New Lounger
    Join Date
    Mar 2006
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: update date/time in a cell only when macro is run (11.8012.6568/2003)

    basically i have a cell in the excel doc that the formula = now() is in, and it updates the date/time in the cell everytime i open the document. i would prefer it to only update the date/time when i run the macro. is this possible?

  4. #4
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: update date/time in a cell only when macro is

    Hi There

    Say the cell you want to update is E1 you could use this code (in bold) or insert it into your currecnt code:

    Sub MyMacro()
    Range("E1").Select
    ActiveCell.Value = Now()



    'Rest of my code goes here

    End Sub

    You could format the cell to read DD/MM/YY HH:MM:SS
    Jerry

  5. #5
    New Lounger
    Join Date
    Mar 2006
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: update date/time in a cell only when macro is

    worked great - Thanks Jerry!!!

  6. #6
    3 Star Lounger
    Join Date
    Aug 2004
    Posts
    361
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: update date/time in a cell only when macro is

    Is there a method of adapting this so not only will the date and time change, but also to record who made the change? For example if the workbook was saved on a common drive and I wanted to know the person who opened it and made the changes as well as the date and time of the change. I know it can't be ActiveCell. Value = NAME() is not correct as this will only bring up the sheet name. Is there a simple way to get the user's name as well? Thanks.

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: update date/time in a cell only when macro is

    Try

    Range("E2") = Application.UserName

    (replace E2 with the cell where you want the username)

  8. #8
    3 Star Lounger
    Join Date
    Aug 2004
    Posts
    361
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: update date/time in a cell only when macro is

    Hans, is there a method to get the Windows Login name? Depending upon how Excel was set up some username is 'generic' while on other machines it is the actual user names. If it could call the name of the person logged in, this would solve the problem. Hopefully <img src=/S/doh.gif border=0 alt=doh width=15 height=15>

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: update date/time in a cell only when macro is

    You can put the code from <post:=446,010>post 446,010</post:> in a module, and use it like this:

    Range("E2") = GetNetUser

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •