Update cell

[Martin]

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

 

[Alan]

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.  Ihave
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.

 

[Niek Otten]

Hi Martin,

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

 

[Peter T]

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