Hello Guys,

I've read most of the solution given for the problems. Very impresive
and to be honest you guys very intelligent.

I have a little problem which i believe you guys might be able to give
th perfect solution.

I'm working on excel sheet for my department which gathering
information from various user. I've drafted a row for user id.
Currently they type in manually but i want that cell to capture the
username from system or ms office. So what is formula and where should
i add the formula? If there's formula i need to add in vb module, what
is the formula i need to add in the cell?

Thanks in advanced guys.

To return the currently logged in username copy/paste this Function to a
General module in your workbook.

Function User()
Application.Volatile
User = Environ("UserName")
End Function

In any cell on a worksheet enter =User() to return the name of whoever
has the workbook open currently.

This may not supply you with what you need.

Do you want a list of all users who have opened the workbook?


Gord Dibben MS Excel MVP

On Mon, 9 Mar 2009 14:56:22 -0700 (PDT), "
wrote:

Hello Guys,

I've read most of the solution given for the problems. Very impresive
and to be honest you guys very intelligent.

I have a little problem which i believe you guys might be able to give
th perfect solution.

I'm working on excel sheet for my department which gathering
information from various user. I've drafted a row for user id.
Currently they type in manually but i want that cell to capture the
username from system or ms office. So what is formula and where should
i add the formula? If there's formula i need to add in vb module, what
is the formula i need to add in the cell?

Thanks in advanced guys.

On Mar 9, 11:10*pm, Gord Dibben <gorddibbATshawDOTca wrote:
To return the currently logged inusernamecopy/paste this Function to a
General module in your workbook.

Function User()
* * Application.Volatile
* * User = Environ("UserName")
End Function

In any cell on a worksheet enter * =User() * to return the name of whoever
has the workbook open currently.

This may not supply you with what you need.

Do you want a list of all users who have opened the workbook?

Gord Dibben *MS Excel MVP

On Mon, 9 Mar 2009 14:56:22 -0700 (PDT), "



wrote:
Hello Guys,

I've read most of the solution given for the problems. Very impresive
and to be honest you guys very intelligent.

I have a little problem which i believe you guys might be able to give
th perfect solution.

I'm working on excel sheet for my department which gathering
information from various user. I've drafted a row for user id.
Currently they type in manually but i want that cell to capture the
usernamefrom system or ms office. So what is formula and where should
i add the formula? If there's formula i need to add in vb module, what
is the formula i need to add in the cell?

Thanks in advanced guys.- Hide quoted text -

- Show quoted text -

Gord,

Thanks for your help. As you said, i need the list of all users who
open the work book. So what the formula, where to add?
As well as the spread sheet save the details.

Cheers Gord

On Tue, 10 Mar 2009 18:31:12 -0700 (PDT), "
wrote:

Thanks for your help. As you said, i need the list of all users who
open the work book. So what the formula, where to add?
As well as the spread sheet save the details.


Forget about the UDF and entering a formula in a cell.

We can do it all through VBA Workbook_Open event.

This code will be placed in Thisworkbook module of your workbook.

Private Sub Workbook_Open()
Set rng1 = Worksheets("Sheet1").Cells(Rows.Count, 1) _
.End(xlUp).Offset(1, 0)
With rng1
.Value = Environ("Username")
.Offset(0, 1).Value = Format(Now, "dd/mm/yyyy hh:mm:ss")
End With
End Sub

A list of login names and date/time of those who open the workbook will be
placed in Column A of Sheet1(adjust sheet name to suit) starting at A2

You could add more code to ensure the workbook got saved before closing.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
ThisWorkbook.Save
End Sub


Gord

On Mar 11, 6:03*pm, Gord Dibben <gorddibbATshawDOTca wrote:
On Tue, 10 Mar 2009 18:31:12 -0700 (PDT), "

wrote:
Thanks for your help. As you said, i need the list of all users who
open the work book. So what the formula, where to add?
As well as the spread sheet save the details.

Forget about the UDF and entering a formula in a cell.

We can do it all through VBA Workbook_Open event.

This code will be placed in Thisworkbook module of your workbook.

Private Sub Workbook_Open()
Set rng1 = Worksheets("Sheet1").Cells(Rows.Count, 1) _
* * *.End(xlUp).Offset(1, 0)
With rng1
* * .Value = Environ("Username")
* * .Offset(0, 1).Value = Format(Now, "dd/mm/yyyy hh:mm:ss")
End With
End Sub

A list of login names and date/time of those who open the workbook will be
placed in Column A of Sheet1(adjust sheet name to suit) starting at A2

You could add more code to ensure the workbook got saved before closing.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
ThisWorkbook.Save
End Sub

Gord






Hi Gord,

Thanks for all the help which is very much useful. Just little
problem.
I've put the book is shared mood. When 5 user login at same time, its
only display 3 also when the same user login again, its override the
previous time.
Whats the formula to display everyone login to the wook book and also
keep track how many times they login?

Cheers Gord.

On Mar 11, 6:03*pm, Gord Dibben <gorddibbATshawDOTca wrote:
On Tue, 10 Mar 2009 18:31:12 -0700 (PDT), "

wrote:
Thanks for your help. As you said, i need the list of all users who
open the work book. So what the formula, where to add?
As well as the spread sheet save the details.

Forget about the UDF and entering a formula in a cell.

We can do it all through VBA Workbook_Open event.

This code will be placed in Thisworkbook module of your workbook.

Private Sub Workbook_Open()
Set rng1 = Worksheets("Sheet1").Cells(Rows.Count, 1) _
* * *.End(xlUp).Offset(1, 0)
With rng1
* * .Value = Environ("Username")
* * .Offset(0, 1).Value = Format(Now, "dd/mm/yyyy hh:mm:ss")
End With
End Sub

A list of login names and date/time of those who open the workbook will be
placed in Column A of Sheet1(adjust sheet name to suit) starting at A2

You could add more code to ensure the workbook got saved before closing.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
ThisWorkbook.Save
End Sub

Gord






Hi Gord,

Thanks for all the help which is very much useful. Just little
problem.
I've put the book is shared mood. When 5 user login at same time, its
only display 3 also when the same user login again, its override the
previous time.
Whats the formula to display everyone login to the wook book and also
keep track how many times they login?

Cheers Gord.

I don't work with shared workbooks and have no way of testing on a multiple
user system.

Three users get recorded as logged in when they open the book.

Two do not.

I'm not sure what to make of that.

Also, the overwrite should not happen.

The names and times should stack up in Column A from A2 downwards.

As long as the workbook gets saved that is. It should whenever the workbook
is closed if you added the beforeclose event.


Gord

On Sat, 14 Mar 2009 13:13:19 -0700 (PDT), "
wrote:

On Mar 11, 6:03*pm, Gord Dibben <gorddibbATshawDOTca wrote:
On Tue, 10 Mar 2009 18:31:12 -0700 (PDT), "

wrote:
Thanks for your help. As you said, i need the list of all users who
open the work book. So what the formula, where to add?
As well as the spread sheet save the details.

Forget about the UDF and entering a formula in a cell.

We can do it all through VBA Workbook_Open event.

This code will be placed in Thisworkbook module of your workbook.

Private Sub Workbook_Open()
Set rng1 = Worksheets("Sheet1").Cells(Rows.Count, 1) _
* * *.End(xlUp).Offset(1, 0)
With rng1
* * .Value = Environ("Username")
* * .Offset(0, 1).Value = Format(Now, "dd/mm/yyyy hh:mm:ss")
End With
End Sub

A list of login names and date/time of those who open the workbook will be
placed in Column A of Sheet1(adjust sheet name to suit) starting at A2

You could add more code to ensure the workbook got saved before closing.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
ThisWorkbook.Save
End Sub

Gord






Hi Gord,

Thanks for all the help which is very much useful. Just little
problem.
I've put the book is shared mood. When 5 user login at same time, its
only display 3 also when the same user login again, its override the
previous time.
Whats the formula to display everyone login to the wook book and also
keep track how many times they login?

Cheers Gord.