On 22 Jan, 11:31, Martin wrote:
Hello,

I have this formula in a cell: *

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

The UserName() Function is this, which is in a seperate module:

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

The problem is when the user opens the file the formula doesnt work. *I have
tried ActiveSheet.calulcate or a macro to re-enter the formula but it doesnt
work. *The only way it works is for the user to click in the cell and press
enter, then it updates with the correct username.

Does anyone know how to programatically fix this?

Many thanks in advance.

Martin

One solution, 'though possibly not the one you were looking for, is to
discard your function and use the Workbook_Open event instead to write
the user name to a cell on a worksheet. Then use the worksheet
reference in your lookup rather than the function.