How to capture Username into specific cells

[dev.subramaniam]

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.

 

[Gord Dibben]

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

 

[dev.subramaniam]

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







- 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

 

[Gord Dibben]

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

 

[dev.subramaniam]

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.

 

[dev.subramaniam]

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.

 

[Gord Dibben]

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