Is it Possible Q
 

Is it possible to Time stamp a sheet to indicate everytime a worksheet has
been opened?

I have a file which users should be using daily, but I suspect they are not
(although they tell me they are!) so I was wondering if I could build a
database within that file that would timestamp an area of a worksheet every
time the file was actually opened (as opposed to everytime it was saved, as
they might not have to save it everytime), say A1 in Sheet1, so the second
time they access/open the file in would stamp A2 in Sheet1 etc etc

Hope someone can help

Is it Possible Q
 

Private Sub Workbook_Open()
Dim iLast As Long
With Worksheets("Sheet1")
iLast = .Cells(.Rows.Count, "A").End(xlUp).Row + 1
If iLast = 2 Then
If .Range("A1") = "" Then
iLast = 1
End If
End If
.Range("A" & iLast).Value = Format(Now, "dd mmm yyyy hh:mm:ss")
End With
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

"John" wrote in message
...
Is it possible to Time stamp a sheet to indicate everytime a worksheet has
been opened?

I have a file which users should be using daily, but I suspect they are
not
(although they tell me they are!) so I was wondering if I could build a
database within that file that would timestamp an area of a worksheet
every
time the file was actually opened (as opposed to everytime it was saved,
as
they might not have to save it everytime), say A1 in Sheet1, so the second
time they access/open the file in would stamp A2 in Sheet1 etc etc

Hope someone can help

Is it Possible Q
 

right click the excel icon in the upper left near fileview codeleft window
select workbookright window select OPENtype in

sheets("sheet1").range("a1")=date
SAVE

--
Don Guillett
SalesAid Software

"John" wrote in message
...
Is it possible to Time stamp a sheet to indicate everytime a worksheet has
been opened?

I have a file which users should be using daily, but I suspect they are
not
(although they tell me they are!) so I was wondering if I could build a
database within that file that would timestamp an area of a worksheet
every
time the file was actually opened (as opposed to everytime it was saved,
as
they might not have to save it everytime), say A1 in Sheet1, so the second
time they access/open the file in would stamp A2 in Sheet1 etc etc

Hope someone can help

Is it Possible Q
 

Bob offered good code but it does allow manual input as well to type in
yesterday's date

i would run this in the "background" without visibility

if you are ok with Excel

you might want to include a hidden worksheet for this information
additionally if you set teh font to "white" even if they open the sheet they
will not see the records

If you are good with Excel or VBA you may want it to write to a separate
file or even send you an email notification when fiile is opened

"Bob Phillips" wrote:

Private Sub Workbook_Open()
Dim iLast As Long
With Worksheets("Sheet1")
iLast = .Cells(.Rows.Count, "A").End(xlUp).Row + 1
If iLast = 2 Then
If .Range("A1") = "" Then
iLast = 1
End If
End If
.Range("A" & iLast).Value = Format(Now, "dd mmm yyyy hh:mm:ss")
End With
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

"John" wrote in message
...
Is it possible to Time stamp a sheet to indicate everytime a worksheet has
been opened?

I have a file which users should be using daily, but I suspect they are
not
(although they tell me they are!) so I was wondering if I could build a
database within that file that would timestamp an area of a worksheet
every
time the file was actually opened (as opposed to everytime it was saved,
as
they might not have to save it everytime), say A1 in Sheet1, so the second
time they access/open the file in would stamp A2 in Sheet1 etc etc

Hope someone can help

Is it Possible Q
 

Try this....

There must be a worsheet in the workbook with the title "Access Log":

Private Sub Workbook_Open()
x = Sheets("Access Log").Cells(1, 2) ' cell B1 holds log count
Sheets("Access Log").Cells(x + 2, 1) = Format(Now(), "ddd-dd/mmm/yy
hh:mm:ssampm")
Sheets("Access Log").Cells(x + 2, 2) = Application.UserName ' ** Display
User who opened file
Sheets("Access Log").Cells(1, 2) = x + 1 ' Increment Log count ' **
Increment Log count
Sheets("Access Log").Visible = xlVeryHidden ' *** Hides the Access Log
worksheet
ActiveWorkbook.Save ' *** Save Changes to Log ** Note User may also save
after any user changes
End Sub

Hope this helps.. adding everyones views I hope.
Andrew


"Vacation's Over" wrote in message
...
| Bob offered good code but it does allow manual input as well to type in
| yesterday's date
|
| i would run this in the "background" without visibility
|
| if you are ok with Excel
|
| you might want to include a hidden worksheet for this information
| additionally if you set teh font to "white" even if they open the sheet
they
| will not see the records
|
| If you are good with Excel or VBA you may want it to write to a separate
| file or even send you an email notification when fiile is opened
|
| "Bob Phillips" wrote:
|
| Private Sub Workbook_Open()
| Dim iLast As Long
| With Worksheets("Sheet1")
| iLast = .Cells(.Rows.Count, "A").End(xlUp).Row + 1
| If iLast = 2 Then
| If .Range("A1") = "" Then
| iLast = 1
| End If
| End If
| .Range("A" & iLast).Value = Format(Now, "dd mmm yyyy hh:mm:ss")
| End With
| 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
|
| "John" wrote in message
| ...
| Is it possible to Time stamp a sheet to indicate everytime a worksheet
has
| been opened?
|
| I have a file which users should be using daily, but I suspect they
are
| not
| (although they tell me they are!) so I was wondering if I could build
a
| database within that file that would timestamp an area of a worksheet
| every
| time the file was actually opened (as opposed to everytime it was
saved,
| as
| they might not have to save it everytime), say A1 in Sheet1, so the
second
| time they access/open the file in would stamp A2 in Sheet1 etc etc
|
| Hope someone can help
|
|
|
|
|
|

Is it Possible Q
 

Thanks Bob, only problem I see is that the Timestamp only shows if you
actually save the file. The file I have doesn't necessary need saving, the
user just needs to open to view its contents, so if they do that I won't see
the times/dates etc

"Bob Phillips" wrote in message
...
Private Sub Workbook_Open()
Dim iLast As Long
With Worksheets("Sheet1")
iLast = .Cells(.Rows.Count, "A").End(xlUp).Row + 1
If iLast = 2 Then
If .Range("A1") = "" Then
iLast = 1
End If
End If
.Range("A" & iLast).Value = Format(Now, "dd mmm yyyy hh:mm:ss")
End With
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

"John" wrote in message
...
Is it possible to Time stamp a sheet to indicate everytime a worksheet
has
been opened?

I have a file which users should be using daily, but I suspect they are
not
(although they tell me they are!) so I was wondering if I could build a
database within that file that would timestamp an area of a worksheet
every
time the file was actually opened (as opposed to everytime it was saved,
as
they might not have to save it everytime), say A1 in Sheet1, so the
second
time they access/open the file in would stamp A2 in Sheet1 etc etc

Hope someone can help

Is it Possible Q
 

Andrew, thanks for your code, but it doesn't seem to do anything. I have
placed it within code for sheet "Access Log", but it doesn' seem to do
anthing i.e. I have a blank sheet for "Access Log" when I open / close it


"Andrew" <NoToSPAM@home wrote in message
...
Try this....

There must be a worsheet in the workbook with the title "Access Log":

Private Sub Workbook_Open()
x = Sheets("Access Log").Cells(1, 2) ' cell B1 holds log count
Sheets("Access Log").Cells(x + 2, 1) = Format(Now(), "ddd-dd/mmm/yy
hh:mm:ssampm")
Sheets("Access Log").Cells(x + 2, 2) = Application.UserName ' ** Display
User who opened file
Sheets("Access Log").Cells(1, 2) = x + 1 ' Increment Log count ' **
Increment Log count
Sheets("Access Log").Visible = xlVeryHidden ' *** Hides the Access Log
worksheet
ActiveWorkbook.Save ' *** Save Changes to Log ** Note User may also
save
after any user changes
End Sub

Hope this helps.. adding everyones views I hope.
Andrew


"Vacation's Over" wrote in
message
...
| Bob offered good code but it does allow manual input as well to type in
| yesterday's date
|
| i would run this in the "background" without visibility
|
| if you are ok with Excel
|
| you might want to include a hidden worksheet for this information
| additionally if you set teh font to "white" even if they open the sheet
they
| will not see the records
|
| If you are good with Excel or VBA you may want it to write to a separate
| file or even send you an email notification when fiile is opened
|
| "Bob Phillips" wrote:
|
| Private Sub Workbook_Open()
| Dim iLast As Long
| With Worksheets("Sheet1")
| iLast = .Cells(.Rows.Count, "A").End(xlUp).Row + 1
| If iLast = 2 Then
| If .Range("A1") = "" Then
| iLast = 1
| End If
| End If
| .Range("A" & iLast).Value = Format(Now, "dd mmm yyyy
hh:mm:ss")
| End With
| 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
|
| "John" wrote in message
| ...
| Is it possible to Time stamp a sheet to indicate everytime a
worksheet
has
| been opened?
|
| I have a file which users should be using daily, but I suspect they
are
| not
| (although they tell me they are!) so I was wondering if I could
build
a
| database within that file that would timestamp an area of a
worksheet
| every
| time the file was actually opened (as opposed to everytime it was
saved,
| as
| they might not have to save it everytime), say A1 in Sheet1, so the
second
| time they access/open the file in would stamp A2 in Sheet1 etc etc
|
| Hope someone can help
|
|
|
|
|
|

Is it Possible Q
 

Andrew, I think I've got it to work but now the sheet is hidden, and I can't
seem to find it through the usual route of FORMAT-SHEET-UNHIDE (as the
unhide is greyed out). Any clues?




"Andrew" <NoToSPAM@home wrote in message
...
Try this....

There must be a worsheet in the workbook with the title "Access Log":

Private Sub Workbook_Open()
x = Sheets("Access Log").Cells(1, 2) ' cell B1 holds log count
Sheets("Access Log").Cells(x + 2, 1) = Format(Now(), "ddd-dd/mmm/yy
hh:mm:ssampm")
Sheets("Access Log").Cells(x + 2, 2) = Application.UserName ' ** Display
User who opened file
Sheets("Access Log").Cells(1, 2) = x + 1 ' Increment Log count ' **
Increment Log count
Sheets("Access Log").Visible = xlVeryHidden ' *** Hides the Access Log
worksheet
ActiveWorkbook.Save ' *** Save Changes to Log ** Note User may also
save
after any user changes
End Sub

Hope this helps.. adding everyones views I hope.
Andrew


"Vacation's Over" wrote in
message
...
| Bob offered good code but it does allow manual input as well to type in
| yesterday's date
|
| i would run this in the "background" without visibility
|
| if you are ok with Excel
|
| you might want to include a hidden worksheet for this information
| additionally if you set teh font to "white" even if they open the sheet
they
| will not see the records
|
| If you are good with Excel or VBA you may want it to write to a separate
| file or even send you an email notification when fiile is opened
|
| "Bob Phillips" wrote:
|
| Private Sub Workbook_Open()
| Dim iLast As Long
| With Worksheets("Sheet1")
| iLast = .Cells(.Rows.Count, "A").End(xlUp).Row + 1
| If iLast = 2 Then
| If .Range("A1") = "" Then
| iLast = 1
| End If
| End If
| .Range("A" & iLast).Value = Format(Now, "dd mmm yyyy
hh:mm:ss")
| End With
| 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
|
| "John" wrote in message
| ...
| Is it possible to Time stamp a sheet to indicate everytime a
worksheet
has
| been opened?
|
| I have a file which users should be using daily, but I suspect they
are
| not
| (although they tell me they are!) so I was wondering if I could
build
a
| database within that file that would timestamp an area of a
worksheet
| every
| time the file was actually opened (as opposed to everytime it was
saved,
| as
| they might not have to save it everytime), say A1 in Sheet1, so the
second
| time they access/open the file in would stamp A2 in Sheet1 etc etc
|
| Hope someone can help
|
|
|
|
|
|

Is it Possible Q
 

Andrew got your code working fully and must say its a neat bit of code. Only
one thing I'm trying to change the date format and am having no luck, it
visually returns the following

Sun-25/Sep/0505: 43: 37PM

How can I get it to look like this-

Sun - 25/09/05 at 05:43:37 pm

I've tried changing the code but it doesn't seem to matter, it still comes
out like the first one





"Andrew" <NoToSPAM@home wrote in message
...
Try this....

There must be a worsheet in the workbook with the title "Access Log":

Private Sub Workbook_Open()
x = Sheets("Access Log").Cells(1, 2) ' cell B1 holds log count
Sheets("Access Log").Cells(x + 2, 1) = Format(Now(), "ddd-dd/mmm/yy
hh:mm:ssampm")
Sheets("Access Log").Cells(x + 2, 2) = Application.UserName ' ** Display
User who opened file
Sheets("Access Log").Cells(1, 2) = x + 1 ' Increment Log count ' **
Increment Log count
Sheets("Access Log").Visible = xlVeryHidden ' *** Hides the Access Log
worksheet
ActiveWorkbook.Save ' *** Save Changes to Log ** Note User may also
save
after any user changes
End Sub

Hope this helps.. adding everyones views I hope.
Andrew


"Vacation's Over" wrote in
message
...
| Bob offered good code but it does allow manual input as well to type in
| yesterday's date
|
| i would run this in the "background" without visibility
|
| if you are ok with Excel
|
| you might want to include a hidden worksheet for this information
| additionally if you set teh font to "white" even if they open the sheet
they
| will not see the records
|
| If you are good with Excel or VBA you may want it to write to a separate
| file or even send you an email notification when fiile is opened
|
| "Bob Phillips" wrote:
|
| Private Sub Workbook_Open()
| Dim iLast As Long
| With Worksheets("Sheet1")
| iLast = .Cells(.Rows.Count, "A").End(xlUp).Row + 1
| If iLast = 2 Then
| If .Range("A1") = "" Then
| iLast = 1
| End If
| End If
| .Range("A" & iLast).Value = Format(Now, "dd mmm yyyy
hh:mm:ss")
| End With
| 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
|
| "John" wrote in message
| ...
| Is it possible to Time stamp a sheet to indicate everytime a
worksheet
has
| been opened?
|
| I have a file which users should be using daily, but I suspect they
are
| not
| (although they tell me they are!) so I was wondering if I could
build
a
| database within that file that would timestamp an area of a
worksheet
| every
| time the file was actually opened (as opposed to everytime it was
saved,
| as
| they might not have to save it everytime), say A1 in Sheet1, so the
second
| time they access/open the file in would stamp A2 in Sheet1 etc etc
|
| Hope someone can help
|
|
|
|
|
|

Is it Possible Q
 

unhide is now solved


"John" wrote in message
...
Andrew, I think I've got it to work but now the sheet is hidden, and I
can't seem to find it through the usual route of FORMAT-SHEET-UNHIDE (as
the unhide is greyed out). Any clues?




"Andrew" <NoToSPAM@home wrote in message
...
Try this....

There must be a worsheet in the workbook with the title "Access Log":

Private Sub Workbook_Open()
x = Sheets("Access Log").Cells(1, 2) ' cell B1 holds log count
Sheets("Access Log").Cells(x + 2, 1) = Format(Now(), "ddd-dd/mmm/yy
hh:mm:ssampm")
Sheets("Access Log").Cells(x + 2, 2) = Application.UserName ' ** Display
User who opened file
Sheets("Access Log").Cells(1, 2) = x + 1 ' Increment Log count ' **
Increment Log count
Sheets("Access Log").Visible = xlVeryHidden ' *** Hides the Access Log
worksheet
ActiveWorkbook.Save ' *** Save Changes to Log ** Note User may also
save
after any user changes
End Sub

Hope this helps.. adding everyones views I hope.
Andrew


"Vacation's Over" wrote in
message
...
| Bob offered good code but it does allow manual input as well to type in
| yesterday's date
|
| i would run this in the "background" without visibility
|
| if you are ok with Excel
|
| you might want to include a hidden worksheet for this information
| additionally if you set teh font to "white" even if they open the sheet
they
| will not see the records
|
| If you are good with Excel or VBA you may want it to write to a
separate
| file or even send you an email notification when fiile is opened
|
| "Bob Phillips" wrote:
|
| Private Sub Workbook_Open()
| Dim iLast As Long
| With Worksheets("Sheet1")
| iLast = .Cells(.Rows.Count, "A").End(xlUp).Row + 1
| If iLast = 2 Then
| If .Range("A1") = "" Then
| iLast = 1
| End If
| End If
| .Range("A" & iLast).Value = Format(Now, "dd mmm yyyy
hh:mm:ss")
| End With
| 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
|
| "John" wrote in message
| ...
| Is it possible to Time stamp a sheet to indicate everytime a
worksheet
has
| been opened?
|
| I have a file which users should be using daily, but I suspect they
are
| not
| (although they tell me they are!) so I was wondering if I could
build
a
| database within that file that would timestamp an area of a
worksheet
| every
| time the file was actually opened (as opposed to everytime it was
saved,
| as
| they might not have to save it everytime), say A1 in Sheet1, so the
second
| time they access/open the file in would stamp A2 in Sheet1 etc etc
|
| Hope someone can help
|
|
|
|
|
|

Is it Possible Q
 

Got it I was modifying the same code I had in a different worksheet ...
doooh


"John" wrote in message
...
Andrew got your code working fully and must say its a neat bit of code.
Only one thing I'm trying to change the date format and am having no luck,
it visually returns the following

Sun-25/Sep/0505: 43: 37PM

How can I get it to look like this-

Sun - 25/09/05 at 05:43:37 pm

I've tried changing the code but it doesn't seem to matter, it still comes
out like the first one





"Andrew" <NoToSPAM@home wrote in message
...
Try this....

There must be a worsheet in the workbook with the title "Access Log":

Private Sub Workbook_Open()
x = Sheets("Access Log").Cells(1, 2) ' cell B1 holds log count
Sheets("Access Log").Cells(x + 2, 1) = Format(Now(), "ddd-dd/mmm/yy
hh:mm:ssampm")
Sheets("Access Log").Cells(x + 2, 2) = Application.UserName ' ** Display
User who opened file
Sheets("Access Log").Cells(1, 2) = x + 1 ' Increment Log count ' **
Increment Log count
Sheets("Access Log").Visible = xlVeryHidden ' *** Hides the Access Log
worksheet
ActiveWorkbook.Save ' *** Save Changes to Log ** Note User may also
save
after any user changes
End Sub

Hope this helps.. adding everyones views I hope.
Andrew


"Vacation's Over" wrote in
message
...
| Bob offered good code but it does allow manual input as well to type in
| yesterday's date
|
| i would run this in the "background" without visibility
|
| if you are ok with Excel
|
| you might want to include a hidden worksheet for this information
| additionally if you set teh font to "white" even if they open the sheet
they
| will not see the records
|
| If you are good with Excel or VBA you may want it to write to a
separate
| file or even send you an email notification when fiile is opened
|
| "Bob Phillips" wrote:
|
| Private Sub Workbook_Open()
| Dim iLast As Long
| With Worksheets("Sheet1")
| iLast = .Cells(.Rows.Count, "A").End(xlUp).Row + 1
| If iLast = 2 Then
| If .Range("A1") = "" Then
| iLast = 1
| End If
| End If
| .Range("A" & iLast).Value = Format(Now, "dd mmm yyyy
hh:mm:ss")
| End With
| 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
|
| "John" wrote in message
| ...
| Is it possible to Time stamp a sheet to indicate everytime a
worksheet
has
| been opened?
|
| I have a file which users should be using daily, but I suspect they
are
| not
| (although they tell me they are!) so I was wondering if I could
build
a
| database within that file that would timestamp an area of a
worksheet
| every
| time the file was actually opened (as opposed to everytime it was
saved,
| as
| they might not have to save it everytime), say A1 in Sheet1, so the
second
| time they access/open the file in would stamp A2 in Sheet1 etc etc
|
| Hope someone can help
|
|
|
|
|
|

Is it Possible Q
 

Sorry John, Had to go out.. for a while.
should of mentioned the sub goes in the **workbook** code section (not for
the worksheet)

the part:
Sheets("Access Log").Visible = xlVeryHidden ' *** Hides the Access Log
worksheet
this means the sheet can not be unhidden from the menu's.
Instead go to MS visual basic editor, click the worksheet on the right to
select it.
press F4(to open the properties window), at the bottom you will see
visibility.
you can make it either, xlVisible, xlHidden or xlVeryHidden.
the later stops everyday users from viewing/changing the sheet.

Change this line of code, to the format you would like displayed:
Sheets("Access Log").Cells(x + 2, 2) = Format(Now(), "ddd - dd/mm/yy at
hh:mm:ss ampm")
' **** The value of function Now()i.e. todays date/time formated to
everything within the quotes.

Hope this Helps. Andrew


"John" wrote in message
...
| Got it I was modifying the same code I had in a different worksheet ...
| doooh
|
| "John" wrote in message
| ...
| Andrew got your code working fully and must say its a neat bit of code.
| Only one thing I'm trying to change the date format and am having no
luck,
| it visually returns the following
|
| Sun-25/Sep/0505: 43: 37PM
|
| How can I get it to look like this-
|
| Sun - 25/09/05 at 05:43:37 pm
|
| I've tried changing the code but it doesn't seem to matter, it still
comes
| out like the first one