Update cell automatically

[Martin]

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

 

[Martin]

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