Update cell automatically
 

Hello,

I have this code that gets the username:

Function UserName() As String
UserName = Environ("UserName")
End Function

I call this code in a cell as follows:

="Summary for " & VLOOKUP(UserName(),Sheet1!B34:C100,2,FALSE)

The workbook is a shared workbook and this works fine for me however it
doesnt update for any other user. When they open it, if they click in the
formula bar of the cell and press enter then it updates.

Does anyone know how to update the cell automatically?

Thanks in advance,

Martin

Update cell automatically
 

Excel usually calculates on open, however you can put
Code:
--------------------
Private Sub Worksheet_Activate()
Me.Calculate
End Sub
--------------------
in the worksheet code module, that should do the trick!Martin;191464 Wrote:
Hello,

I have this code that gets the username:

Function UserName() As String
UserName = Environ("UserName")
End Function

I call this code in a cell as follows:

="Summary for " & VLOOKUP(UserName(),Sheet1!B34:C100,2,FALSE)

The workbook is a shared workbook and this works fine for me however
it
doesnt update for any other user. When they open it, if they click in
the
formula bar of the cell and press enter then it updates.

Does anyone know how to update the cell automatically?

Thanks in advance,

Martin


--
The Code Cage Team

Regards,
The Code Cage Team
'The Code Cage' (http://www.thecodecage.com)
------------------------------------------------------------------------
The Code Cage Team's Profile: http://www.thecodecage.com/forumz/member.php?userid=2
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=52757

Update cell automatically
 

Thanks for the reply. I tried this and I can still open the file and as it
worked for me originally I dont know if it has had any effect as nobody else
can even open the file now, it says Automation Error (440).

I have looked this up and all the relevant references are ticked so I am at
a complete loss as to why nobody can open it except me.

Do you have any ideas on this?

Thank you for your help

Martin

"The Code Cage Team" wrote:


Excel usually calculates on open, however you can put
Code:
--------------------
Private Sub Worksheet_Activate()
Me.Calculate
End Sub
--------------------
in the worksheet code module, that should do the trick!Martin;191464 Wrote:
Hello,

I have this code that gets the username:

Function UserName() As String
UserName = Environ("UserName")
End Function

I call this code in a cell as follows:

="Summary for " & VLOOKUP(UserName(),Sheet1!B34:C100,2,FALSE)

The workbook is a shared workbook and this works fine for me however
it
doesnt update for any other user. When they open it, if they click in
the
formula bar of the cell and press enter then it updates.

Does anyone know how to update the cell automatically?

Thanks in advance,

Martin


--
The Code Cage Team

Regards,
The Code Cage Team
'The Code Cage' (http://www.thecodecage.com)
------------------------------------------------------------------------
The Code Cage Team's Profile: http://www.thecodecage.com/forumz/member.php?userid=2
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=52757