Option Explicit
Private UndoA1
Private UndoB1
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("A1:B1")) Is Nothing Then Exit Sub
If Target.Cells.CountLarge > 1 Then
Application.EnableEvents = False
Range("A1") = UndoA1
Range("B1") = UndoB1
Application.EnableEvents = True
Exit Sub
End If
Application.EnableEvents = False
Select Case Target.Address
Case Is = "$A$1"
If UndoA1 <> "" Then
Application.Undo
Else
UndoA1 = Target.Value
End If
Case Is = "$B$1"
If UndoB1 <> "" Then
Application.Undo
Else
UndoB1 = Target.Value
End If
End Select
Application.EnableEvents = True
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Intersect(Target, Range("A1:B1")) Is Nothing Then Exit Sub
Select Case Target.Address
Case Is = "$A$1"
UndoA1 = Target.Value
Case Is = "$B$1"
UndoB1 = Target.Value
End Select
End Sub
isn't a constructive response, as said I tested it; so ... where did you paste the macros ? what other macros do you have in the project ? You said that A1 and B1 where input cells (manual unput) or are they calculated cells (with formulas) ?but it did not work
I have opened a new workbook, pasted it into the sheet module, but i did not get it though. The cells have formula please. Thank you for your response thoughisn't a constructive response, as said I tested it; so ... where did you paste the macros ? what other macros do you have in the project ? You said that A1 and B1 where input cells (manual unput) or are they calculated cells (with formulas) ?
Please, open a new workbook, paste my macros in Sheet1's vbe module and retry using cells A1 and B1 of that sheet.
This requires a completely different approach; these aren't 'input cells' they are 'calculated cells'. I do not know if with my modest knowledge I'm able to create a solution.The cells have formula
Thank youThis requires a completely different approach; these aren't 'input cells' they are 'calculated cells'. I do not know if with my modest knowledge I'm able to create a solution.
Global UndoA1
Global UndoB1
Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Intersect(Target, Range("A1:B1")) Is Nothing Then Exit Sub
If Target.Cells.CountLarge > 1 Then Exit Sub
Select Case Target.Address
Case Is = "$A$1"
UndoA1 = Target.Formula
Case Is = "$B$1"
UndoB1 = Target.Formula
End Select
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("A1:B1")) Is Nothing Then Exit Sub
If Target.Cells.CountLarge > 1 Then
Application.EnableEvents = False
Application.Undo
Application.EnableEvents = True
Exit Sub
End If
Application.EnableEvents = False
Select Case Target.Address
Case Is = "$A$1"
Range("A1") = UndoA1
Case Is = "$B$1"
Range("B1") = UndoB1
End Select
Application.EnableEvents = True
End Sub
Thank You, I will test i and give you the feedbackHad another try, please test it. I'm assuming that there are 2 formulas in A1 and B1, so:
In a Standard module paste these 2 global variables:then in the sheet's module paste this:Code:Global UndoA1 Global UndoB1
Note that you will not be able to perform a multicell paste or delete whenever one of cells A1 or B1 is in the selection.Code:Option Explicit Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Intersect(Target, Range("A1:B1")) Is Nothing Then Exit Sub If Target.Cells.CountLarge > 1 Then Exit Sub Select Case Target.Address Case Is = "$A$1" UndoA1 = Target.Formula Case Is = "$B$1" UndoB1 = Target.Formula End Select End Sub Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Target, Range("A1:B1")) Is Nothing Then Exit Sub If Target.Cells.CountLarge > 1 Then Application.EnableEvents = False Application.Undo Application.EnableEvents = True Exit Sub End If Application.EnableEvents = False Select Case Target.Address Case Is = "$A$1" Range("A1") = UndoA1 Case Is = "$B$1" Range("B1") = UndoB1 End Select Application.EnableEvents = True End Sub
Till now the only issue I managed to create is when trying modify a multicell selection when A1 or B1 has the focus.