Hi all,

I know that I have asked this question before but I cannot find the post
so I decided to ask it again.

Could someone please tell me how do I go about recording the username,
date and time the workbook was opened and have this information recorded
continuously starting from A1 row 1 and moving downwards on a hidden
worksheet.

i.e.

A1 Username1 01/01/06 1.00pm
A2 Username2 01/01/06 2.13pm
A3 Username3 06/01/06 5.00am

Thanx in advance.

'-----------------------------------------------------------------
Private Sub Workbook_Open()
'-----------------------------------------------------------------
Dim iRow As Long
Const SH_NAME As String = "Hidden"

With Worksheets(SH_NAME)
iRow = .Cells(.Rows.Count, "A").End(xlUp).Row
If iRow < 1 Or .Range("A1") < "" Then
iRow = iRow + 1
End If
.Cells(iRow, "A").Value = Environ("UserName") & " " & _
Format(Now, "dd/mm/yy hh:mmAM/PM")
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

(remove xxx from email address if mailing direct)

"Maddoktor" wrote in message
...
Hi all,

I know that I have asked this question before but I cannot find the post
so I decided to ask it again.

Could someone please tell me how do I go about recording the username,
date and time the workbook was opened and have this information recorded
continuously starting from A1 row 1 and moving downwards on a hidden
worksheet.

i.e.

A1 Username1 01/01/06 1.00pm
A2 Username2 01/01/06 2.13pm
A3 Username3 06/01/06 5.00am

Thanx in advance.

Maddokter,

I use:
Private Sub Workbook_Open()

Open ThisWorkbook.Path & "\usage.log" For Append As #1
Print #1, Application.UserName, Now
Close #1

End Sub


Now if you wanted this to be hidden then change the path and filename
to suit somewhere maybe a network drive or something where the user
wouldnt look.

HTH

Duncan

Thanx Bob, it work great.

Just wondering how do I expand the coding so that the username, date and
time are in three seperate columns so I can later filter individual columns.

Maddoktor



Bob Phillips wrote:
'-----------------------------------------------------------------
Private Sub Workbook_Open()
'-----------------------------------------------------------------
Dim iRow As Long
Const SH_NAME As String = "Hidden"

With Worksheets(SH_NAME)
iRow = .Cells(.Rows.Count, "A").End(xlUp).Row
If iRow < 1 Or .Range("A1") < "" Then
iRow = iRow + 1
End If
.Cells(iRow, "A").Value = Environ("UserName") & " " & _
Format(Now, "dd/mm/yy hh:mmAM/PM")
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

'-----------------------------------------------------------------
Private Sub Workbook_Open()
'-----------------------------------------------------------------
Dim iRow As Long
Const SH_NAME As String = "Hidden"

With Worksheets(SH_NAME)
iRow = .Cells(.Rows.Count, "A").End(xlUp).Row
If iRow < 1 Or .Range("A1") < "" Then
iRow = iRow + 1
End If
.Cells(iRow, "A").Value = Environ("UserName")
.Cells(iRow, "B").Value =Format(Date, "dd/mm/yy")
.Cells(iRow, "C").Value = Format(Time, "hh:mmAM/PM")
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

(remove xxx from email address if mailing direct)

"Maddoktor" wrote in message
...
Thanx Bob, it work great.

Just wondering how do I expand the coding so that the username, date and
time are in three seperate columns so I can later filter individual
columns.

Maddoktor



Bob Phillips wrote:
'-----------------------------------------------------------------
Private Sub Workbook_Open()
'-----------------------------------------------------------------
Dim iRow As Long
Const SH_NAME As String = "Hidden"

With Worksheets(SH_NAME)
iRow = .Cells(.Rows.Count, "A").End(xlUp).Row
If iRow < 1 Or .Range("A1") < "" Then
iRow = iRow + 1
End If
.Cells(iRow, "A").Value = Environ("UserName") & " " & _
Format(Now, "dd/mm/yy hh:mmAM/PM")
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

Thanx Bob ... worked perfectly.

Maybe you could help me to expand it even further and I am not sure if I
should start another thread.

My workbook has 5 worksheets. The first three worksheets are designed as
individual menus/links to other worksheets that are hidden. i.e. one
worksheet is the menu options for finance, one for marketing, one for
design. I would like for one of the three individual menus (worksheets)
to be displayed depending on the username of whom opens the workbook.
i.e. if user AB opens the workbook, then the finance menu (worksheet) is
displayed that has links to the finance worksheets (that were hidden)
and then for these hidden worksheets to be displayed.

Is this at all possible?

Maddoktor


Bob Phillips wrote:
'-----------------------------------------------------------------
Private Sub Workbook_Open()
'-----------------------------------------------------------------
Dim iRow As Long
Const SH_NAME As String = "Hidden"

With Worksheets(SH_NAME)
iRow = .Cells(.Rows.Count, "A").End(xlUp).Row
If iRow < 1 Or .Range("A1") < "" Then
iRow = iRow + 1
End If
.Cells(iRow, "A").Value = Environ("UserName")
.Cells(iRow, "B").Value =Format(Date, "dd/mm/yy")
.Cells(iRow, "C").Value = Format(Time, "hh:mmAM/PM")
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

'-----------------------------------------------------------------
Private Sub Workbook_Open()
'-----------------------------------------------------------------
Dim sUser As String

sUser = Environ("UserName")
Select Case sUser
Case "AB":
With Worksheets("Finance")
.Visible = xlSheetVisible
Worksheets("Marketing").Visible = False
Worksheets("Design").Visible = False
.Activate
End With
Case "MN":
With Worksheets("Marketing")
.Visible = xlSheetVisible
Worksheets("Finance").Visible = False
Worksheets("Design").Visible = False
.Activate
End With
Case "XY":
With Worksheets("Design")
.Visible = xlSheetVisible
Worksheets("Finance").Visible = False
Worksheets("Marketing").Visible = False
.Activate
End With
End Select

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 xxx from email address if mailing direct)

"Maddoktor" wrote in message
...
Thanx Bob ... worked perfectly.

Maybe you could help me to expand it even further and I am not sure if I
should start another thread.

My workbook has 5 worksheets. The first three worksheets are designed as
individual menus/links to other worksheets that are hidden. i.e. one
worksheet is the menu options for finance, one for marketing, one for
design. I would like for one of the three individual menus (worksheets)
to be displayed depending on the username of whom opens the workbook.
i.e. if user AB opens the workbook, then the finance menu (worksheet) is
displayed that has links to the finance worksheets (that were hidden)
and then for these hidden worksheets to be displayed.

Is this at all possible?

Maddoktor


Bob Phillips wrote:
'-----------------------------------------------------------------
Private Sub Workbook_Open()
'-----------------------------------------------------------------
Dim iRow As Long
Const SH_NAME As String = "Hidden"

With Worksheets(SH_NAME)
iRow = .Cells(.Rows.Count, "A").End(xlUp).Row
If iRow < 1 Or .Range("A1") < "" Then
iRow = iRow + 1
End If
.Cells(iRow, "A").Value = Environ("UserName")
.Cells(iRow, "B").Value =Format(Date, "dd/mm/yy")
.Cells(iRow, "C").Value = Format(Time, "hh:mmAM/PM")
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

Hi Bob,

This is Sriram and i utilized your code below by which you helped Maddoktor.
But when I'm on this macro excel file, if some other excel file (any excel
file) is also opened and want to verify some data by going to that file,
immediately the sheets which has been opened for the particular User is
getting closed and when I come back again to this file those sheets are not
getting visible again. I don't know what's wrong in this.

I request you to kindly clear this and help me out, because I'm doing a
project for my colleagues in my office by making the file available in the
local server.

Kindly resolve this issue at the earliest.

Regards,
Sriram


"Bob Phillips" wrote:


'-----------------------------------------------------------------
Private Sub Workbook_Open()
'-----------------------------------------------------------------
Dim sUser As String

sUser = Environ("UserName")
Select Case sUser
Case "AB":
With Worksheets("Finance")
.Visible = xlSheetVisible
Worksheets("Marketing").Visible = False
Worksheets("Design").Visible = False
.Activate
End With
Case "MN":
With Worksheets("Marketing")
.Visible = xlSheetVisible
Worksheets("Finance").Visible = False
Worksheets("Design").Visible = False
.Activate
End With
Case "XY":
With Worksheets("Design")
.Visible = xlSheetVisible
Worksheets("Finance").Visible = False
Worksheets("Marketing").Visible = False
.Activate
End With
End Select

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 xxx from email address if mailing direct)

"Maddoktor" wrote in message
...
Thanx Bob ... worked perfectly.

Maybe you could help me to expand it even further and I am not sure if I
should start another thread.

My workbook has 5 worksheets. The first three worksheets are designed as
individual menus/links to other worksheets that are hidden. i.e. one
worksheet is the menu options for finance, one for marketing, one for
design. I would like for one of the three individual menus (worksheets)
to be displayed depending on the username of whom opens the workbook.
i.e. if user AB opens the workbook, then the finance menu (worksheet) is
displayed that has links to the finance worksheets (that were hidden)
and then for these hidden worksheets to be displayed.

Is this at all possible?

Maddoktor


Bob Phillips wrote:
'-----------------------------------------------------------------
Private Sub Workbook_Open()
'-----------------------------------------------------------------
Dim iRow As Long
Const SH_NAME As String = "Hidden"

With Worksheets(SH_NAME)
iRow = .Cells(.Rows.Count, "A").End(xlUp).Row
If iRow < 1 Or .Range("A1") < "" Then
iRow = iRow + 1
End If
.Cells(iRow, "A").Value = Environ("UserName")
.Cells(iRow, "B").Value =Format(Date, "dd/mm/yy")
.Cells(iRow, "C").Value = Format(Time, "hh:mmAM/PM")
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

As I said in the other post. there is nothing here that will then close
files so you must have some other code affecting it.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Sriram" wrote in message
...
Hi Bob,

This is Sriram and i utilized your code below by which you helped
Maddoktor.
But when I'm on this macro excel file, if some other excel file (any excel
file) is also opened and want to verify some data by going to that file,
immediately the sheets which has been opened for the particular User is
getting closed and when I come back again to this file those sheets are
not
getting visible again. I don't know what's wrong in this.

I request you to kindly clear this and help me out, because I'm doing a
project for my colleagues in my office by making the file available in the
local server.

Kindly resolve this issue at the earliest.

Regards,
Sriram


"Bob Phillips" wrote:


'-----------------------------------------------------------------
Private Sub Workbook_Open()
'-----------------------------------------------------------------
Dim sUser As String

sUser = Environ("UserName")
Select Case sUser
Case "AB":
With Worksheets("Finance")
.Visible = xlSheetVisible
Worksheets("Marketing").Visible = False
Worksheets("Design").Visible = False
.Activate
End With
Case "MN":
With Worksheets("Marketing")
.Visible = xlSheetVisible
Worksheets("Finance").Visible = False
Worksheets("Design").Visible = False
.Activate
End With
Case "XY":
With Worksheets("Design")
.Visible = xlSheetVisible
Worksheets("Finance").Visible = False
Worksheets("Marketing").Visible = False
.Activate
End With
End Select

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 xxx from email address if mailing direct)

"Maddoktor" wrote in message
...
Thanx Bob ... worked perfectly.

Maybe you could help me to expand it even further and I am not sure if
I
should start another thread.

My workbook has 5 worksheets. The first three worksheets are designed
as
individual menus/links to other worksheets that are hidden. i.e. one
worksheet is the menu options for finance, one for marketing, one for
design. I would like for one of the three individual menus
(worksheets)
to be displayed depending on the username of whom opens the workbook.
i.e. if user AB opens the workbook, then the finance menu (worksheet)
is
displayed that has links to the finance worksheets (that were hidden)
and then for these hidden worksheets to be displayed.

Is this at all possible?

Maddoktor


Bob Phillips wrote:
'-----------------------------------------------------------------
Private Sub Workbook_Open()
'-----------------------------------------------------------------
Dim iRow As Long
Const SH_NAME As String = "Hidden"

With Worksheets(SH_NAME)
iRow = .Cells(.Rows.Count, "A").End(xlUp).Row
If iRow < 1 Or .Range("A1") < "" Then
iRow = iRow + 1
End If
.Cells(iRow, "A").Value = Environ("UserName")
.Cells(iRow, "B").Value =Format(Date, "dd/mm/yy")
.Cells(iRow, "C").Value = Format(Time, "hh:mmAM/PM")
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