ActivityLog - UserStatus

  • Thread starter Thread starter Gabriel
  • Start date Start date
G

Gabriel

Hi,

I want to create an activity log sheet. Being a begginer in VBA
programming I just could't figure out what to change in the below code
to save the info.

More precisely, when someone opens the workbook the info appears in
the uLog sheet. But then I want it to be saved and then, record on the
following line when the person closes the workbook. The procedure
should repeat itself when the wb is open and then closed.


____
Private Sub Workbook_Open()

Users = ActiveWorkbook.UserStatus

With Sheets("uLog")
For Row = 1 To UBound(Users, 1)
.Cells(Row, 1) = Users(Row, 1)
.Cells(Row, 2) = Users(Row, 2)
Next
End With

End Sub
_____


Thank you in advance
Gabriel
 
Gabriel

Do you mean that you don't want to overwrite what's already there? If so,
here's one way to do it.

Private Sub Workbook_Open()

Dim Users as Variant
Dim StartRng as Range
Dim Rw as Long

Users = ActiveWorkbook.UserStatus
Set StartRng = Me.Sheets("uLog").Range("A65536").End(xlUp)

For Rw = 1 To UBound(Users, 1)
StartRng.Offset(Rw,0).Value = Users(Rw, 1)
StartRng.Offset(Rw,1).Value = Users(Rw, 2)
Next

End Sub
 
Hi,

I've tested the code. It really works !!!
I have one more question: would it be possible to make it record the
time when the person closes the workbook ?

Thank you
Gabriel
 
Gabriel

Kind of. You can use the Workbook_BeforeClose event to run a macro when the
user closes the workbook. However, if the user cancels the close, it may
record that in your log.

Private Sub Workbook_BeforeClose(Cancel As Boolean)

Me.Sheets("uLog").Range("A65536").End(xlUp).Offset(1, 0).Value = Now

End Sub
 
Back
Top