Macro to record user name and date/time

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

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" <[email protected]> wrote in message
news:[email protected]...
> 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
>
>
>

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
>> 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
>>
>>
>>
>
>

Did you change the value of the constant HIDDEN_SHEET to Sheet3?

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Maddoktor" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> > news:[email protected]...
> >> 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
> >>
> >>
> >>
> >
> >
>
>

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" <[email protected]> wrote in message
news:[email protected]...
> Did you change the value of the constant HIDDEN_SHEET to Sheet3?
>
> --
> HTH
>
> Bob Phillips
>
> (remove nothere from email address if mailing direct)
>
> "Maddoktor" <[email protected]> wrote in message
> news:[email protected]...
>> 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" <[email protected]> wrote in message
>> news:[email protected]...
>> > 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" <[email protected]> wrote in message
>> > news:[email protected]...
>> >> 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
>> >>
>> >>
>> >>
>> >
>> >
>>
>>
>
>

I think Bob is gone for the day but what he meant was change the
Constant declaration i.e

Private iNextRow As Long
Const HIDDEN_SHEET As String = "Sheet3" '<<<Changed

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

Hope this helps
Rowan

Maddoktor wrote:
> 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" <[email protected]> wrote in message
> news:[email protected]...
>
>>Did you change the value of the constant HIDDEN_SHEET to Sheet3?
>>
>>--
>>HTH
>>
>>Bob Phillips
>>
>>(remove nothere from email address if mailing direct)
>>
>>"Maddoktor" <[email protected]> wrote in message
>>news:[email protected]...
>>
>>>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" <[email protected]> wrote in message
>>>news:[email protected]...
>>>
>>>>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" <[email protected]> wrote in message
>>>>news:[email protected]...
>>>>
>>>>>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
>>>>>
>>>>>
>>>>>
>>>>
>>>>
>>>
>>
>
>

Thanx Rowan,

It worked :-)

I would like to expand it a little further. The username appears in column A
and the date/time in column B, which is great. I would like the cell
co-ordinates (i.e. D2) and the cell data (before being modified) that have
been modified in columns C and D respectively and the cell's modified data
in column E.

For example: If Sheet1 cell D2=1000, and someone modifies this cell so that
cell D2 now reads 500, I would like my hidden sheet to record:

Column A - Username
Column B - Date/Time
Column C - D2 (cell co-ordinates)
Column D - 1000 (original data - before being modified)
Column E - 500 (modified data - new data that had been entered)

What I am trying to do is keep a running report of a workbook being
modified, by whom, and what they have changed.

I hope this is possible to accomplish.

Thanx in advance.

Maddoktor



"Rowan Drummond" <[email protected]> wrote in message
news:%[email protected]...
>I think Bob is gone for the day but what he meant was change the Constant
>declaration i.e
>
> Private iNextRow As Long
> Const HIDDEN_SHEET As String = "Sheet3" '<<<Changed
>
> 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
>
> Hope this helps
> Rowan
>
> Maddoktor wrote:
>> 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" <[email protected]> wrote in message
>> news:[email protected]...
>>
>>>Did you change the value of the constant HIDDEN_SHEET to Sheet3?
>>>
>>>--
>>>HTH
>>>
>>>Bob Phillips
>>>
>>>(remove nothere from email address if mailing direct)
>>>
>>>"Maddoktor" <[email protected]> wrote in message
>>>news:[email protected]...
>>>
>>>>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" <[email protected]> wrote in message
>>>>news:[email protected]...
>>>>
>>>>>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" <[email protected]> wrote in message
>>>>>news:[email protected]...
>>>>>
>>>>>>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
>>>>>>
>>>>>>
>>>>>>
>>>>>
>>>>>
>>>>
>>>
>>