PDA

View Full Version : [SOLVED] Log In-Out Sheet



IgnBan
03-22-2008, 10:11 AM
Good day everybody!

I have a logIn Sheet that captures user UserName, ComputerName, DateLoggedIn, TimeLoggedIn and TimeLoggedOut. The example code was summited by Simon LLoyd (thanks Lloyd) now I need to modify it. When user logs out, a pop msg window ask if you want to save the changes, and if you click no (workbooks will be opening read-only user not supposed to modify it) user supposed to click no, well, this removes the loogedIn information on the Sheet. How can I overcome this problem, either by avoiding the pop msg window or by recoding the logIn and out information no matter what the user does.

One more question on column "F" I want to print the time in minutes the user was logged in (incode)

Sample WB Attached

Hope everybody is enjoying the long holiday weekend!

Thanks! :thumb

Bob Phillips
03-22-2008, 10:47 AM
Save the logging information in a separate text file, and make the Excel file read-only.

IgnBan
03-22-2008, 11:16 AM
XLD, thanks for replaying. I'm looking the KB for code samples how to keep a separate text file and can't find an example. Can you give me some clues how I can do this, or direct me to a web-page to research the topic.

Bob Phillips
03-22-2008, 11:36 AM
Private Sub Workbook_Open()
Dim FileNumber As Long
FileNumber = FreeFile ' Get unused file
' number.
Open ThisWorkbook.Path & Application.PathSeparator & "LogFile.txt" For Append As #FileNumber
Write #FileNumber, Environ("username"), Environ("computername"), Format(Now, "dd mmm yyyy hh:mm:ss")
Close #FileNumber
End Sub

IgnBan
03-22-2008, 11:40 AM
If I insert this code in the LogTimePost workbook:

Private Sub Workbook_Open()
Open LogTimePost.Path & "\usage.log" For Append As #1
Print #1, Application.UserName, Now
Close #1
End Sub

How can I modify it so it can give in the text file besides User Name ComputerName, DateLoggedIn, TimeLoggedIn, TimeLoggedOut and the difference between the time loggedIn and Out in minutes, in another words the time user was logged?

IgnBan
03-22-2008, 11:41 AM
Sorry XLD, I guess I was writing my replay when you answered my post.

IgnBan
03-22-2008, 12:10 PM
How can I append the logOut time to the text file and calculate the time the user was logged using both logIn and Out time?

IgnBan
03-22-2008, 12:15 PM
I posted the question above and the post didnt bump up!?!?

Bob Phillips
03-22-2008, 12:15 PM
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim FileNumber As Long
FileNumber = FreeFile ' Get unused file
' number.
Open ThisWorkbook.Path & Application.PathSeparator & "LogFile.txt" For Append As #FileNumber
Print #FileNumber, "Out:" & Environ("username"), Environ("computername"), "Format(Now, "; dd; mmm; yyyy; hh: mm: ss ")"
Close #FileNumber
End Sub

Private Sub Workbook_Open()
Dim FileNumber As Long
FileNumber = FreeFile ' Get unused file
' number.
Open ThisWorkbook.Path & Application.PathSeparator & "LogFile.txt" For Append As #FileNumber
Print #FileNumber, "In:" & Environ("username"), Environ("computername"), "Format(Now, "; dd; mmm; yyyy; hh: mm: ss ")"
Close #FileNumber
End Sub

IgnBan
03-22-2008, 02:13 PM
Thanks for the help XLD...I want to be sure I following the proper procedures; This code should be place in the workbook module,.. when I do and try to compile it is giving me and error .
Sub or Function not defined;
..highlighting the minute formating.


Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim FileNumber As Long
FileNumber = FreeFile ' Get unused file
' number.
Open ThisWorkbook.Path & Application.PathSeparator & "LogFile.txt" For Append As #FileNumber
Print #FileNumber, "Out:" & Environ("username"), Environ("computername"), "Format(Now, "; dd; mmm; yyyy; hh: mm: ss ")"
Close #FileNumber
End Sub

When I try to open or close the workbook , gives me the same error in both Subs.

Attached WB

lucas
03-22-2008, 02:58 PM
I don't know if this helps or not Jose......changed Bob's format a little....

Option Explicit
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim FileNumber As Long
FileNumber = FreeFile ' Get unused file
' number.
Open ThisWorkbook.Path & Application.PathSeparator & "LogFile.txt" For Append As #FileNumber
Print #FileNumber, "Out:" & Environ("username"), Environ("computername"), Format(Now, "dd mmm yyyy hh:mm:ss")
Close #FileNumber
End Sub

Private Sub Workbook_Open()
Dim FileNumber As Long
FileNumber = FreeFile ' Get unused file
' number.
Open ThisWorkbook.Path & Application.PathSeparator & "LogFile.txt" For Append As #FileNumber
Print #FileNumber, "In:" & Environ("username"), Environ("computername"), Format(Now, "dd mmm yyyy hh:mm:ss")
Close #FileNumber
End Sub

Bob Phillips
03-22-2008, 03:06 PM
I've no idea how we got that mess, but



"Format(Now, "; dd; mmm; yyyy; hh: mm: ss ")"


should be



"Format(Now, "dd-mmm-yyyy hh:mm:ss ")"

lucas
03-22-2008, 03:15 PM
It must have happened when you posted it Bob......system is pretty good but not perfect.

IgnBan
03-22-2008, 04:44 PM
Lucas that will doit, it fixed it, thanks so much.
Xld Thanks for the code, this appending will do the "time logged" by showing the time in and out next to the other, visually it can tell how long the user reviewed the workbook. Thank you guys!

Bob Phillips
03-22-2008, 04:57 PM
It must have happened when you posted it Bob......system is pretty good but not perfect.

No, I just looked at my workbook, I introduced it into there somehow.

Bob Phillips
03-22-2008, 04:58 PM
Lucas that will doit, it fixed it, thanks so much.
Xld Thanks for the code, this appending will do the "time logged" by showing the time in and out next to the other, visually it can tell how long the user reviewed the workbook. Thank you guys!

Personally, Iwould think that the log file is a perfect andidate for further analysis in Excel, probably using a pivot table/pivot charts.