Public feuille$
Public cellule$
Private Sub Workbook_SheetDeactivate(ByVal Fl As Object)
Dim af As Worksheet
Set af = ActiveSheet
With Application: .EnableEvents = 0: .ScreenUpdating = 0: End With
Fl.Activate
feuille = ActiveSheet.Name
cellule = ActiveCell.Address
af.Activate
With Application: .ScreenUpdating = 0: .EnableEvents = 1: End With
End Sub
Sheets(feuille).Range(cellule)
Sub Macro5()
If feuille = "" Then Exit Sub
With Application
.ScreenUpdating = 0
Range("A4").Copy
Sheets(feuille).Range(cellule).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
.CutCopyMode = False
.ScreenUpdating = 1
End With
End Sub
Private Sub Workbook_Open()
If ActiveSheet.Type = xlWorksheet Then ActiveCell.Name = "memcel"
End Sub
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
If ActiveSheet.Type <> xlWorksheet Then Exit Sub
If TypeName([memcel]) = "Range" Then Sh.[A4].Copy [memcel]
ActiveCell.Name = "memcel"
End Sub
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
ActiveCell.Name = "memcel"
End Sub
(...)
Il peut y avoir des feuille autres que des feuilles de calcul dans le classeur (Graphique).
(...)
Dim memcel 'mémorise la variable
Private Sub Workbook_Open()
If ActiveSheet.Type = xlWorksheet Then Set memcel = ActiveCell
End Sub
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
If ActiveSheet.Type <> xlWorksheet Then Exit Sub
If Not memcel Is Nothing Then Sh.[A4].Copy memcel
Set memcel = ActiveCell
End Sub
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
Set memcel = ActiveCell
End Sub
Sub TestVariable()
If TypeName(memcel) = "Range" Then
MsgBox "La cellule 'memcel' existe..."
Application.OnTime Now, "ThisWorkbook.TestVariable"
End 'réinitialise tout
Else
MsgBox "La cellule 'memcel' n'existe plus..."
End If
End Sub