A macro to populate date/time/user

[rouzacct]

hello everyone

I posted earlier and got some amazing help with the other macro i needed.

I tried to modify that macro to do what i want it to do in this other sheet
but i cant get it working

would you please help me
i think its best to start from scratch than to modify the other macro

I need a macro that would auto populate DATE/TIME/USER depending on

if anything is written in a cell in (A:A) to populate date in "C" Time in "D"
and user in "E"

and if anything is written in G:G to populate date in "I" and Time in "J"

and if anything is written in K:K to populate date in "L" and Time in "M" and User in "N"

 

[bertie]

Place this code into the relevant sheet module.

Edit the date and time formats to suit.
I have assumed the cells clear if entries are deleted. If not needed removed the ELSE part of the IF statements.
UserName is dependant on how MSOffice was setup on pc.

[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] Worksheet_Change([COLOR=darkblue]ByVal[/COLOR] Target [COLOR=darkblue]As[/COLOR] Range)
   
   [COLOR=green]'only trigger on columns A, G and K[/COLOR]
   [COLOR=darkblue]If[/COLOR] Target.Column <> 1 _
      And Target.Column <> 7 _
      And Target.Column <> 11 [COLOR=darkblue]Then[/COLOR]
      [COLOR=darkblue]Exit[/COLOR] [COLOR=darkblue]Sub[/COLOR]
   [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
   
   [COLOR=green]'limit user to selecting one cell[/COLOR]
   [COLOR=darkblue]If[/COLOR] Target.Count > 1 [COLOR=darkblue]Then[/COLOR]
      [COLOR=darkblue]Exit[/COLOR] [COLOR=darkblue]Sub[/COLOR]
   [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
   
   [COLOR=darkblue]Select[/COLOR] [COLOR=darkblue]Case[/COLOR] Target.Column
      
      [COLOR=green]'Column A[/COLOR]
      [COLOR=darkblue]Case[/COLOR] [COLOR=darkblue]Is[/COLOR] = 1
         [COLOR=darkblue]If[/COLOR] Target.Value <> "" [COLOR=darkblue]Then[/COLOR]
            Target.Offset(, 2).Value = Format(Now, "dd-mmm-yyyy")
            Target.Offset(, 3).Value = Format(Now, "hh:mm:ss")
            Target.Offset(, 4).Value = Application.UserName
         [COLOR=darkblue]Else[/COLOR]
            Target.Offset(, 2).Value = ""
            Target.Offset(, 3).Value = ""
            Target.Offset(, 4).Value = ""
         [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
      
      
      [COLOR=green]'Column G[/COLOR]
      [COLOR=darkblue]Case[/COLOR] [COLOR=darkblue]Is[/COLOR] = 7
         [COLOR=darkblue]If[/COLOR] Target.Value <> "" [COLOR=darkblue]Then[/COLOR]
            Target.Offset(, 2).Value = Format(Now, "dd-mmm-yyyy")
            Target.Offset(, 3).Value = Format(Now, "hh:mm:ss")
         [COLOR=darkblue]Else[/COLOR]
            Target.Offset(, 2).Value = ""
            Target.Offset(, 3).Value = ""
         [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
      
      
      [COLOR=green]'Column K[/COLOR]
      [COLOR=darkblue]Case[/COLOR] [COLOR=darkblue]Else[/COLOR]
         [COLOR=darkblue]If[/COLOR] Target.Value <> "" [COLOR=darkblue]Then[/COLOR]
            Target.Offset(, 2).Value = Format(Now, "dd-mmm-yyyy")
            Target.Offset(, 3).Value = Format(Now, "hh:mm:ss")
         [COLOR=darkblue]Else[/COLOR]
            Target.Offset(, 2).Value = ""
            Target.Offset(, 3).Value = ""
         [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
   [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Select[/COLOR]
End [COLOR=darkblue]Sub[/COLOR]

 

[rouzacct]

Thanks a Billion !!

its exactly what i needed

Thank You

!!