another code not working
 

i hope someone can help...

im writing a code to give me a log of time of open .. time user spent and
which user is using the worksheet..

its not working...

what have i missed out please?


Private Sub Workbook_Open()

Dim TStart As Long 'Timer - Start
Dim TStop As Long 'Timer - Stop
Dim MyPath As String 'Full Path of Workbook
Dim PW As String 'Sheet Protection Password
Dim DV As String 'Dialog Value for MsgBox
Dim x As Long
Sub Workbook_BeforeClose(Cancel As Boolean)
PW = "test"
If Dir("\TAS\Excess Inventory\") < "" Then
Application.ScreenUpdating = False
MyPath = Application.ActiveWorkbook.FullName
ChDir "\TAS\Logs"
Workbooks.Open Filename:="\TAS\Logs\ExcessLog.xls"
Workbooks("ExcessLog.xls").Sheets("UserLog").Unpro tect PW
Range("A2").Select
x = 2
'** Get to empty row
While Trim(ActiveCell.Offset(0, 0).Value) < ""
ActiveCell.Offset(1, 0).Select
x = x + 1
Wend
TStop = Timer
Range("A" & x).Value = Date
Range("B" & x).Value = MyPath
Range("C" & x).Value = Application.UserName
Range("D" & x).Value = ((TStop - TStart) / 60)
Workbooks("ExcessLog.xls").Sheets("UserLog").Prote ct PW
Workbooks("ExcessLog.xls").Save
Workbooks("ExcessLog.xls").Close
Application.ScreenUpdating = True
Else
Dim msg, Style, title, response, mystring
msg = "Contact the HelpDesk and request access to the \ drive - Slight
Problem..."
Style = vbOKOnly + vbApplicationModal
title = "Information"
response = MsgBox(msg, Style, title)
If response = vbOK Then
TStart = Timer


End If
End Sub
--
thank you in advance if someone helps out...

another code not working
 

To start, you have a workbook open event that dimensions a timer, and then
you go straight to the before close event. Looks like you are missing a lot
of code in your example.
--
-John
Please rate when your question is answered to help us and others know what
is helpful.


"Moh" wrote:

i hope someone can help...

im writing a code to give me a log of time of open .. time user spent and
which user is using the worksheet..

its not working...

what have i missed out please?


Private Sub Workbook_Open()

Dim TStart As Long 'Timer - Start
Dim TStop As Long 'Timer - Stop
Dim MyPath As String 'Full Path of Workbook
Dim PW As String 'Sheet Protection Password
Dim DV As String 'Dialog Value for MsgBox
Dim x As Long
Sub Workbook_BeforeClose(Cancel As Boolean)
PW = "test"
If Dir("\TAS\Excess Inventory\") < "" Then
Application.ScreenUpdating = False
MyPath = Application.ActiveWorkbook.FullName
ChDir "\TAS\Logs"
Workbooks.Open Filename:="\TAS\Logs\ExcessLog.xls"
Workbooks("ExcessLog.xls").Sheets("UserLog").Unpro tect PW
Range("A2").Select
x = 2
'** Get to empty row
While Trim(ActiveCell.Offset(0, 0).Value) < ""
ActiveCell.Offset(1, 0).Select
x = x + 1
Wend
TStop = Timer
Range("A" & x).Value = Date
Range("B" & x).Value = MyPath
Range("C" & x).Value = Application.UserName
Range("D" & x).Value = ((TStop - TStart) / 60)
Workbooks("ExcessLog.xls").Sheets("UserLog").Prote ct PW
Workbooks("ExcessLog.xls").Save
Workbooks("ExcessLog.xls").Close
Application.ScreenUpdating = True
Else
Dim msg, Style, title, response, mystring
msg = "Contact the HelpDesk and request access to the \ drive - Slight
Problem..."
Style = vbOKOnly + vbApplicationModal
title = "Information"
response = MsgBox(msg, Style, title)
If response = vbOK Then
TStart = Timer


End If
End Sub
--
thank you in advance if someone helps out...

another code not working
 

can you give the rest of the missing code please?
-

thank you


"John Bundy" wrote:

To start, you have a workbook open event that dimensions a timer, and then
you go straight to the before close event. Looks like you are missing a lot
of code in your example.
--
-John
Please rate when your question is answered to help us and others know what
is helpful.


"Moh" wrote:

i hope someone can help...

im writing a code to give me a log of time of open .. time user spent and
which user is using the worksheet..

its not working...

what have i missed out please?


Private Sub Workbook_Open()

Dim TStart As Long 'Timer - Start
Dim TStop As Long 'Timer - Stop
Dim MyPath As String 'Full Path of Workbook
Dim PW As String 'Sheet Protection Password
Dim DV As String 'Dialog Value for MsgBox
Dim x As Long
Sub Workbook_BeforeClose(Cancel As Boolean)
PW = "test"
If Dir("\TAS\Excess Inventory\") < "" Then
Application.ScreenUpdating = False
MyPath = Application.ActiveWorkbook.FullName
ChDir "\TAS\Logs"
Workbooks.Open Filename:="\TAS\Logs\ExcessLog.xls"
Workbooks("ExcessLog.xls").Sheets("UserLog").Unpro tect PW
Range("A2").Select
x = 2
'** Get to empty row
While Trim(ActiveCell.Offset(0, 0).Value) < ""
ActiveCell.Offset(1, 0).Select
x = x + 1
Wend
TStop = Timer
Range("A" & x).Value = Date
Range("B" & x).Value = MyPath
Range("C" & x).Value = Application.UserName
Range("D" & x).Value = ((TStop - TStart) / 60)
Workbooks("ExcessLog.xls").Sheets("UserLog").Prote ct PW
Workbooks("ExcessLog.xls").Save
Workbooks("ExcessLog.xls").Close
Application.ScreenUpdating = True
Else
Dim msg, Style, title, response, mystring
msg = "Contact the HelpDesk and request access to the \ drive - Slight
Problem..."
Style = vbOKOnly + vbApplicationModal
title = "Information"
response = MsgBox(msg, Style, title)
If response = vbOK Then
TStart = Timer


End If
End Sub
--
thank you in advance if someone helps out...

another code not working
 

You have two "sub" statements with only one "end sub". I would first get the
code working by making the subroutine a regualr sub routine (not
workbook_open) by placing the code in a regular module and change tthe name
to something other than workbook_open. Then debug the code. Then go back
and change the name of the function to workbook_open.

"Moh" wrote:

i hope someone can help...

im writing a code to give me a log of time of open .. time user spent and
which user is using the worksheet..

its not working...

what have i missed out please?


Private Sub Workbook_Open()

Dim TStart As Long 'Timer - Start
Dim TStop As Long 'Timer - Stop
Dim MyPath As String 'Full Path of Workbook
Dim PW As String 'Sheet Protection Password
Dim DV As String 'Dialog Value for MsgBox
Dim x As Long
Sub Workbook_BeforeClose(Cancel As Boolean)
PW = "test"
If Dir("\TAS\Excess Inventory\") < "" Then
Application.ScreenUpdating = False
MyPath = Application.ActiveWorkbook.FullName
ChDir "\TAS\Logs"
Workbooks.Open Filename:="\TAS\Logs\ExcessLog.xls"
Workbooks("ExcessLog.xls").Sheets("UserLog").Unpro tect PW
Range("A2").Select
x = 2
'** Get to empty row
While Trim(ActiveCell.Offset(0, 0).Value) < ""
ActiveCell.Offset(1, 0).Select
x = x + 1
Wend
TStop = Timer
Range("A" & x).Value = Date
Range("B" & x).Value = MyPath
Range("C" & x).Value = Application.UserName
Range("D" & x).Value = ((TStop - TStart) / 60)
Workbooks("ExcessLog.xls").Sheets("UserLog").Prote ct PW
Workbooks("ExcessLog.xls").Save
Workbooks("ExcessLog.xls").Close
Application.ScreenUpdating = True
Else
Dim msg, Style, title, response, mystring
msg = "Contact the HelpDesk and request access to the \ drive - Slight
Problem..."
Style = vbOKOnly + vbApplicationModal
title = "Information"
response = MsgBox(msg, Style, title)
If response = vbOK Then
TStart = Timer


End If
End Sub
--
thank you in advance if someone helps out...

another code not working
 

Well i don't know what exactly you want to do, but i don't see you ever start
your timer. With the nature of what you are doing, i would probably create a
hidden sheet with timestamps, on workbookopen put the time in and user name,
then add time out when they exit, then calculate off of that.
--
-John
Please rate when your question is answered to help us and others know what
is helpful.


"Moh" wrote:

can you give the rest of the missing code please?
-

thank you


"John Bundy" wrote:

To start, you have a workbook open event that dimensions a timer, and then
you go straight to the before close event. Looks like you are missing a lot
of code in your example.
--
-John
Please rate when your question is answered to help us and others know what
is helpful.


"Moh" wrote:

i hope someone can help...

im writing a code to give me a log of time of open .. time user spent and
which user is using the worksheet..

its not working...

what have i missed out please?


Private Sub Workbook_Open()

Dim TStart As Long 'Timer - Start
Dim TStop As Long 'Timer - Stop
Dim MyPath As String 'Full Path of Workbook
Dim PW As String 'Sheet Protection Password
Dim DV As String 'Dialog Value for MsgBox
Dim x As Long
Sub Workbook_BeforeClose(Cancel As Boolean)
PW = "test"
If Dir("\TAS\Excess Inventory\") < "" Then
Application.ScreenUpdating = False
MyPath = Application.ActiveWorkbook.FullName
ChDir "\TAS\Logs"
Workbooks.Open Filename:="\TAS\Logs\ExcessLog.xls"
Workbooks("ExcessLog.xls").Sheets("UserLog").Unpro tect PW
Range("A2").Select
x = 2
'** Get to empty row
While Trim(ActiveCell.Offset(0, 0).Value) < ""
ActiveCell.Offset(1, 0).Select
x = x + 1
Wend
TStop = Timer
Range("A" & x).Value = Date
Range("B" & x).Value = MyPath
Range("C" & x).Value = Application.UserName
Range("D" & x).Value = ((TStop - TStart) / 60)
Workbooks("ExcessLog.xls").Sheets("UserLog").Prote ct PW
Workbooks("ExcessLog.xls").Save
Workbooks("ExcessLog.xls").Close
Application.ScreenUpdating = True
Else
Dim msg, Style, title, response, mystring
msg = "Contact the HelpDesk and request access to the \ drive - Slight
Problem..."
Style = vbOKOnly + vbApplicationModal
title = "Information"
response = MsgBox(msg, Style, title)
If response = vbOK Then
TStart = Timer


End If
End Sub
--
thank you in advance if someone helps out...