1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29
| Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo Err_Worksheet_Change
Dim Cell_Cde, Cell_Source, Cell_Destination As Range
Dim Str_Msg As String
Set Cell_Cde = Range("C36")
Set Cell_Source = Range("B36")
Set Cell_Destination = Range("C35")
Str_Msg = "=indirect(" & Cell_Source.Address & ")"
If Intersect(Target, Cell_Cde) Is Nothing Then GoTo Sort_Worksheet_Change
If UCase(Target) = "X" Then
Cell_Destination.FormulaR1C1 = "=indirect(""" & Cell_Source.Address & """)"
Else
Cell_Destination.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveSheet.Paste
Target.Select
End If
Sort_Worksheet_Change:
Exit Sub
Err_Worksheet_Change:
MsgBox (Err.Number & " - " & Err.Description)
Resume Sort_Worksheet_Change
End Sub |
Partager