Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim Wbk As Workbook, NomComplet As String, CelJum As Range
If Target.CountLarge <> 1 Then Exit Sub
If Not Target.HasFormula Then Exit Sub
With Application: .ScreenUpdating = False: .DisplayAlerts = False: .EnableEvents = False: End With
On Error GoTo E
If Replace(Target.FormulaR1C1, "'", "") Like "=?*[[]*]*!R#*C#*" Then
NomComplet = Replace(Replace(Split(Mid$(Target.Formula, 2), "]")(0), "'", ""), "[", "")
If MsgBox("Cette valeur vient du classeur suivant :" & vbLf & NomComplet & vbLf _
& "Avez vous l'intention de la modifier ?", vbExclamation + vbYesNo, _
"Sélection " & Target.Address(False, False, xlA1, False)) = vbNo Then
Me.[A1:B1].Select: GoTo E: End If
Set Wbk = Workbooks.Open(NomComplet)
End If
Set CelJum = Evaluate(Target.Formula)
Target.Value = Target.Value
CelJum.Formula = "=" & Target.Address(True, True, xlA1, True)
If Not Wbk Is Nothing Then Wbk.Close SaveChanges:=True
E: With Application: .EnableEvents = True: .DisplayAlerts = True: End With
End Sub