Auto-update cells defined by UDF

[c_m]

Hello,

I have number of user defined functions used in the spreadsheet. For some reasons, the values are not updated automatically sometimes. I checked into tools-options-calculation-automatic. Are there any other settings i should know of so that the values are updated automatically?

thanks in advance

 

[Paddy1979]

Input this bit of code at the start of your udf

Application.Volatile

 

[c_m]

Thankyou

 

[egiannaros]

I tried using the Volatile Method for a UDF that I have which returns the windows username of the person who opens the file. It doesn't seem to be working for me.

Function UserNameWindows() As String
Application.Volatile
UserNameWindows = Environ("USERNAME")
End Function

The function only works when I click in the cell P3 and Press Shift+F3. Cell P3 reads as follows:

=UserNameWindows()

Is this Volatile Method not working because the Function has no arguments?

Does someone have another method for automating this function so it always updates? Thanks in advance.

 

[Paddy1979]

You could try a different approach.

Private Sub Workbook_Open()
 Sheet1.Range("A1:A10").Value = Application.UserName
End Sub

 

[egiannaros]

You could try a different approach.

Private Sub Workbook_Open()
 Sheet1.Range("A1:A10").Value = Application.UserName
End Sub

I tried this code in the VBA for "Sheet1" , "ThisWorkbook", and in one of my Modules. I couldn't get it to work in any of those 3 scenarios. (I made the change, closed the file and reopened the file.)

Also, it has to be the windows login username, not the office username (because that is just my company's name). My UDF code gets the windows login but I just can't seem to figure out how to have it automatically update when you open the file or recalc.

Does anyone know of another way to do this where it updates automatically?

 

[RoryA]

Application.Volatile will cause your UDF to recalculate when the workbook is recalculated. Assuming you have other formulas that cause a recalculation, your UDF should recalculate too. The code for a Workbook_Open event should go into the ThisWorkbook module of the relevant workbook. If it doesn't run from there, check your macro security settings.

 

[egiannaros]

I have a security password set on the VBA for this file. I thought that just limits who can view and change the VBA, but will that security interfere with either method? Thanks.

 

[RoryA]

No, it shouldn't do.