How to display message box only after pressing Ctrl+S (save) in a particular cell using vba

Question

I have written a code to display old and new values of a cell and then store the message box display data to another sheet one after another...

Option Explicit

Dim OldVals As New Dictionary
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim myCell As Range
    Dim sMsg As String
    Dim rg As Range
    Dim lr As Integer
    For Each myCell In Target
        If OldVals.Exists(myCell.Address) Then
            sMsg = "New value of " & Replace(myCell.Address, "$", "") & " is " & myCell.Value & "; old value was " & OldVals(myCell.Address)
            MsgBox sMsg

            If MsgBox(sMsg) = vbOK Then Set rg = ThisWorkbook.Sheets("Sheet2").Range("A1")

            lr = rg.CurrentRegion.Rows.Count
            rg.Offset(lr, 0).Value = sMsg
        Else
            MsgBox "No old value for " + Replace(myCell.Address, "$", "")

        End If
        OldVals(myCell.Address) = myCell.Value
    Next myCell
End Sub

In my code when I press enter after entering the cell value the message box will come and display the old and new values like below picture --.

But I want to display this message box only after saving the cell value after pressing ctrl+s key not by pressing enter key or any other key . Only after pressing Ctrl+s to save the cell value the message box will come as above but I am not able to figure that out kindly help me.

Answer 1

You can assign Ctrl+s to a Macro

Public Sub SaveWorkbook()
    ActiveWorkbook.Save
    'Your code here
End Sub