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

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.

Hi Martin,

Add 'Application.Volatile' to your function.
Or use NOW() as a dummy argument

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Martin" wrote in message
...
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

Another way, without VBA

Define this name

Name user
Refersto =GET.WORKSPACE(26)&IF(NOW(),"","")

and in your cell simply
="Summary for " & user

Regards,
Peter T


"Martin" wrote in message
...
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