Forum Discussion

Brass Contributor

Jul 25, 2022

Solved

Can we write a VBA Code, that automatically overwrites cells values of a formula on workbook close?

I have a table formula that calculates total amount based on given prices. The trouble is when the prices change, it overwrites the formula of Dates which have already passed, And the ledger neve...

Macro Lock Problem.xlsm22 KB

excel

Excel on mobile

Formulas and Functions

Jul 25, 2022

In the Visual Basic Editor, double-click ThisWorkbook under Microsoft Excel Objects.

Copy the following code into the ThisWorkbook module:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Dim rng As Range
    With Worksheets("Data")
        For Each rng In .Range(.Range("A2"), .Range("A1").End(xlDown))
            If rng.Value <= Date Then
                With rng.Offset(0, 4)
                    .Value = .Value
                End With
            End If
        Next rng
    End With
End Sub

The code will run automatically each time you save the workbook (including when you close it and click Yes to the Save prompt).

PeterBartholomew1

Silver Contributor

Jul 25, 2022

Nishkarsh31

My setting blocked your workbook so I started from scratch. I tried to minimise the use of VBA so I first defined the range to fix by formula

StaticRange
= Top:XLOOKUP(TODAY(),Data[Date],Data[Amount],,-1,-1)

I then used the range within a WorkbookClose event handler within the ThisWorkbook module

Option Explicit

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim rngFixed As Range

    Set rngFixed = Range("StaticRange")
    rngFixed.Value = rngFixed.Value

End Sub

StaticRange.xlsm19 KB

Nishkarsh31

Brass Contributor

Jul 25, 2022

Hi Sir, loved your approach to the problem.

However there would be one issue to using the xlookup approach
At times in the sequence, we write today's date Eg 25th July

Then take a few future orders, let's say 27th July

Then back to 25th July.

This obviously gets sorted later, during any analysis, but based on this approach the 27th Amount will also be copied as value

PeterBartholomew1

Silver Contributor

Jul 25, 2022

Nishkarsh31

You are correct. I wrongly assumed strict date order. It can be salvaged (and I borrowed the use of 'With' from HansVogelaar)

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    SortTable Worksheets("Data").ListObjects("Data")
    With Range("StaticRange")
        .Value = .Value
    End With
End Sub

but the simplicity is lost by the call to the sort function

Function SortTable(LO As ListObject)
' SortTable Macro
    LO.Sort.SortFields.Clear
    LO.Sort.SortFields.Add2 Key _
        :=Range("Data[Date]"), SortOn:=xlSortOnValues, Order:=xlAscending, _
        DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Data").ListObjects("Data").Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Function

which is not pretty!