iis there a way to cause the Get.Cell function to dynamically update

[bearcub]

I am using the Get.Cell Excel 4 macro function to show the actually number format being displayed in a cell (Get.Cell(7,A1)).

I would like the Get.cell Cell to update automatically when I change the cell format. The only way it updates now is if I enter the data in the source or I click and do something in the cell that houses the Get.Cell formula.

I was using the Calculate function in the SelectionChange worksheet event but that doesn't seem to help.

Do you know of what that would cause functions like this to update dynamically without my having to manually perform some action on the impacted cells (both the source and formula cells)?

I was thinking if I could cause some event to happen on the spreadsheet somehow (like using the Calculate VBA function) that it create some sort of movement (like moving a spiderweb) that trigger the function to update automatically.

Has anyone found a solution for this issue over the years?

Thank you for your help,

Michael

 

[shg]

As you've found, changing format doesn't trigger calculation. There's no way short of some exotic windows API.

 

[Jaafar Tribak]

If you are dealing with just one cell- example ( A1 ) then you could use this trick :

Code goes in the ThisWorkbook Module :

Option Explicit

Private WithEvents oCmndBars As CommandBars
Private vPrevNumberFormat As Variant
    
Private Const TARGET_CELL_Addr As String = "Sheet1!$A$1"  [B][COLOR=#008000]' <== Change this cell addr CONST as required.[/COLOR][/B]


Private Sub Workbook_Open()
    Call HookCommandBars
End Sub


Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
   If oCmndBars Is Nothing Then Call HookCommandBars
End Sub


Private Sub HookCommandBars()
    Set oCmndBars = Application.CommandBars
    vPrevNumberFormat = Range(TARGET_CELL_Addr).NumberFormat
    Call oCmndBars_OnUpdate
End Sub


Private Sub oCmndBars_OnUpdate()
    With Application.CommandBars
        .FindControl(ID:=2040).Enabled = Not .FindControl(ID:=2040).Enabled
    End With
    If vPrevNumberFormat <> Range(TARGET_CELL_Addr).NumberFormat Then
        Range(TARGET_CELL_Addr).Parent.Calculate
    End If
    vPrevNumberFormat = Range(TARGET_CELL_Addr).NumberFormat
End Sub

Note:
To make this work for more than one cell, the code will need some tweaking.