Macro to record user name and date/time
Bob,
Here is the code I am working with. I am still getting the same error even
though the error line reads ... With Worksheets(Sheet3).
Option Explicit
Private iNextRow As Long
Const Sheet3 As String = "hidden"
Private Sub Workbook_Open()
With Worksheets(Sheet3)
.Range("A1").Value = Environ("UserName")
.Range("B1").Value = Format(Date + Time, "dd mmm yyyy hh:mm:ss")
End With
iNextRow = 2
End Sub
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
On Error GoTo wb_exit
Application.EnableEvents = False
If Sh.Name < Sheet3 Then
With Worksheets(Sheet3)
.Range("A" & iNextRow).Value = Environ("UserName")
.Range("B" & iNextRow).Value = Format(Date + Time, "dd mmm yyyy
hh: mm: ss ")
End With
End If
wb_exit:
Application.EnableEvents = True
End Sub
Maddoktor
"Bob Phillips" wrote in message
...
Did you change the value of the constant HIDDEN_SHEET to Sheet3?
--
HTH
Bob Phillips
(remove nothere from email address if mailing direct)
"Maddoktor" wrote in message
...
Thanx Bob,
I have one small error coming up. When I open my Workbook (Sheet 3 is the
hidden sheet) an error comes up when the macro is run.
The error is:
Run-time error '9':
Subscript out of range
and when I click on the Debug button, the part of the code that is
highlighted is "With Worksheets(HIDDEN_SHEET)".
Can you tell me what I am doing wrong?
Maddoktor
"Bob Phillips" wrote in message
...
Option Explicit
Private iNextRow As Long
Const HIDDEN_SHEET As String = "hidden"
Private Sub Workbook_Open()
With Worksheets(HIDDEN_SHEET)
.Range("A1").Value = Environ("UserName")
.Range("B1").Value = Format(Date + Time, "dd mmm yyyy hh:mm:ss")
End With
iNextRow = 2
End Sub
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As
Range)
On Error GoTo wb_exit
Application.EnableEvents = False
If Sh.Name < HIDDEN_SHEET Then
With Worksheets(HIDDEN_SHEET)
.Range("A" & iNextRow).Value = Environ("UserName")
.Range("B" & iNextRow).Value = Format(Date + Time, "dd mmm
yyyy
hh:mm:ss")
End With
End If
wb_exit:
Application.EnableEvents = True
End Sub
'This is workbook event code.
'To input this code, right click on the Excel icon on the worksheet
'(or next to the File menu if you maximise your workbooks),
'select View Code from the menu, and paste the code
--
HTH
Bob Phillips
(remove nothere from email address if mailing direct)
"Maddoktor" wrote in message
...
Is it possible to record a macro that when someone on the network
opens
my
workbook that it records the user's name, time and date on a hidden
protected worksheet?
Can this then be expanded further by if the user then changes
something,
either adds, modify, or delete information from my workbook then the
user's
name, date, time and changes (including worksheet name, cell
co-ordinates
(i.e. A1) and information modified from and modified too) are recorded
on
a
hidden protected worksheet?
Thanx in advance.
Maddoktor
|