Track username and date when the sheet open

Question

I'm using VBA to display a welcome message with the username. Additionally I want to keep track of the username (taken from environment variables), date and time in one of the sheets.

For example: If I open the Excel file, it has to be tracked like following in one of the sheet

skiran 11/13/2014 2:00 AM

My code so far

Private Sub Workbook_Open1()
  Dim sName As String
  Dim sTxt As String
  Dim CurrTime As Long
  sName = Environ("UserName")

  CurrTime = Hour(Now)
  Select Case CurrTime
  Case Is > 18
    sTxt = "Good evening, "
  Case Is > 12
    sTxt = "Good afternoon, "
  Case Is < 6
    sTxt = "Good morning, "
  End Select
  msgbox sTxt & sName

End Sub

Answer 1

Just add the following to the end of your code:

dim nextrow as integer

 if Range("'Sheet2'!A2").value = "" then
  nextrow = 2
 else
  nextrow = Range("'Sheet2'!A1").End(xlDown).Row + 1
 end if


Range("'Sheet2'!A" & nextrow ).value = sName
Range("'Sheet2'!B" & nextrow ).value = CurrTime

Change Sheet2 to whatever your sheet name is.

Also I assume that you would have headers for the sheet it is going to. I hope this makes sense. If not ask and I will try to clarify.