| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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... |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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... |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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... |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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... |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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... |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| VBA Code Not Working | Excel Discussion (Misc queries) | |||
| VBA Code Not Working | Excel Discussion (Misc queries) | |||
| Code not working | Excel Programming | |||
| Code not working and can't see why | Excel Discussion (Misc queries) | |||
| Code not working | Excel Programming | |||
Linear Mode
