Recalculate when switching from one workbook to another

[SN00PY]

I use this formula to display the name of the file in a cell, =MID(CELL("filename"),SEARCH("[",CELL("filename"))+1, SEARCH(".",CELL("filename"))-SEARCH("[",CELL("filename"))-1). It works fine, however when working with multiple open workbooks, it displays the file name of the last workbook that performed a calculation. When I switch back to the workbook containing this formula I have to press F9 to get it to display it's own file name. Is there a way to force a recalculation when returning to an open workbook?

 

[SirJB7]

Hi, SNOOPY!

Nota Si el argumento tipo_de_info de la función CELDA es "formato" y más adelante se asigna un formato diferente a la celda a la que se hace referencia, es necesario volver a calcular la hoja de cálculo para poder actualizar los resultados de la función CELDA.

This is a note in the built-in help of the CELL function, it's in Spanish in my original Excel version, in English it should be something like what follows, but you can get the exact text typing =Cell( and pressing the f(x) button at the left of the formula bar:

Note If type_of_info argument of CELL function is "format" and later it's assigned a different format to the referenced cell, it's necessary to recalculate the worksheet to be able to update the CELL function results.

So you could use the Activate event either at workbook level (1st code, both options, for whole workbook of just for specific worksheet) or at worksheet level (2nd code).

Option Explicit
 
Private Sub Workbook_Activate()
    Application.Calculate
End Sub
 
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
    Sh.Calculate
End Sub

Option Explicit
 
Private Sub Worksheet_Activate()
    ActiveSheet.Calculate
End Sub

Regards!

 

[Sam Mathai Chacko]

You'll need to use VBA to achieve that.

In the workbook module use this

Private Sub Workbook_Activate()
    Me.Sheets(1).UsedRange.Calculate
End Sub

 

[Sam Mathai Chacko]

Oops.. sorry SirJB7, I had the window open and I didn't notice your reply.

 

[SirJB7]

@Sam Mathai Chacko
Hi!
Welcome to the club, but I'm afraid you're a junior member while I'm a life member. I use to leave many windows open and when I come back and post, I'm luck if Chandoo hasn't migrated the forums.
And don't ask about my ratio, I'd prefer not to say that at least one every day... Ooops! I said it yet...
Regards!