EXCEL Capture the value of a cell before change
Solved
electron43
-
eriiic Posted messages 24581 Registration date Status Contributor Last intervention -
eriiic Posted messages 24581 Registration date Status Contributor Last intervention -
My objective:
In order to archive the previous content of a cell before a change, I created a macro. However, when multiple cells are selected, I encounter problems. The macro seems to stop working.
(for your information, in the case of multiple selection, I do not wish to archive the values; I just want to display a message)
The Workbook:
I have created two tabs: a "data" tab and a "config" tab.
In the "config" tab, I named a cell "end_of_list" to point to the storage location for the archives.
Here is the macro for Sheet 1 (data):
'***********************************************************
' Declaration of public variables
'***********************************************************
Public selected_cell_value As String
Public history_content As String
'
'
'***********************************************************
' Macro triggered after a change in the content of a cell
'***********************************************************
Private Sub Worksheet_Change(ByVal Target As Range)
' Disabling event-triggered macros to make the
' modification without interfering with Private Sub Worksheet_Change
Application.EnableEvents = False
' Inserting a cell at the bottom of the list
storage_address_previous_value = Application.Range("end_of_list").Address
Application.Range("end_of_list").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
' Archiving the value in the "config" tab
Sheets("config").Range(storage_address_previous_value).Value = history_content
' Reactivating event-triggered macros
Application.EnableEvents = True
End Sub
'
'
'*********************************************************
' Macro triggered after selecting a new cell
'*********************************************************
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
' Storing the value contained in the newly selected cell
' This action allows to memorize the content
' of a cell before making a value change
On Error GoTo sub_macro_01
' The content is stored in the public variable
' "selected_cell_value"
selected_cell_value = Range(Target.Address).Value
history_content = "Tab " & ActiveSheet.Name & " " & Target.Address & " : " & selected_cell_value
GoTo sub_macro_02
'---------------------------------------------------------------
' Subroutine for handling errors and multiple selections
sub_macro_01:
' An error message is stored in the public variable
' "selected_cell_value"
history_content = "Error or multiple cell selection"
' End of the subroutine
'---------------------------------------------------------------
sub_macro_02:
End Sub
'
'
Sub unblock()
'**********************************************************
' Macro to reactivate event-triggered macros
' in case of failure
'**********************************************************
Application.EnableEvents = True
End Sub
'
'
And in ThisWorkBook:
'**********************************************************
' Initialization macro
'**********************************************************
' Upon opening the workbook, cell A1 of the
' "data" sheet is selected. The goal is to capture
' the current value of each newly selected cell
' This value is stored in the public variable
' "selected_cell_value"
Private Sub Workbook_Open()
Application.EnableEvents = False
Sheets("data").Select
Range("A1").Select
Application.EnableEvents = True
End Sub
Thank you in advance to anyone who can spare a little time for me
In order to archive the previous content of a cell before a change, I created a macro. However, when multiple cells are selected, I encounter problems. The macro seems to stop working.
(for your information, in the case of multiple selection, I do not wish to archive the values; I just want to display a message)
The Workbook:
I have created two tabs: a "data" tab and a "config" tab.
In the "config" tab, I named a cell "end_of_list" to point to the storage location for the archives.
Here is the macro for Sheet 1 (data):
'***********************************************************
' Declaration of public variables
'***********************************************************
Public selected_cell_value As String
Public history_content As String
'
'
'***********************************************************
' Macro triggered after a change in the content of a cell
'***********************************************************
Private Sub Worksheet_Change(ByVal Target As Range)
' Disabling event-triggered macros to make the
' modification without interfering with Private Sub Worksheet_Change
Application.EnableEvents = False
' Inserting a cell at the bottom of the list
storage_address_previous_value = Application.Range("end_of_list").Address
Application.Range("end_of_list").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
' Archiving the value in the "config" tab
Sheets("config").Range(storage_address_previous_value).Value = history_content
' Reactivating event-triggered macros
Application.EnableEvents = True
End Sub
'
'
'*********************************************************
' Macro triggered after selecting a new cell
'*********************************************************
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
' Storing the value contained in the newly selected cell
' This action allows to memorize the content
' of a cell before making a value change
On Error GoTo sub_macro_01
' The content is stored in the public variable
' "selected_cell_value"
selected_cell_value = Range(Target.Address).Value
history_content = "Tab " & ActiveSheet.Name & " " & Target.Address & " : " & selected_cell_value
GoTo sub_macro_02
'---------------------------------------------------------------
' Subroutine for handling errors and multiple selections
sub_macro_01:
' An error message is stored in the public variable
' "selected_cell_value"
history_content = "Error or multiple cell selection"
' End of the subroutine
'---------------------------------------------------------------
sub_macro_02:
End Sub
'
'
Sub unblock()
'**********************************************************
' Macro to reactivate event-triggered macros
' in case of failure
'**********************************************************
Application.EnableEvents = True
End Sub
'
'
And in ThisWorkBook:
'**********************************************************
' Initialization macro
'**********************************************************
' Upon opening the workbook, cell A1 of the
' "data" sheet is selected. The goal is to capture
' the current value of each newly selected cell
' This value is stored in the public variable
' "selected_cell_value"
Private Sub Workbook_Open()
Application.EnableEvents = False
Sheets("data").Select
Range("A1").Select
Application.EnableEvents = True
End Sub
Thank you in advance to anyone who can spare a little time for me
2 answers
-
Hello,
you save even if there is no change.
I rather suggest:Private Sub Worksheet_Change(ByVal Target As Range) Dim tmp Static no_events As Boolean If no_events Then Exit Sub If Target.Count > 1 Then Exit Sub tmp = Target no_events = True Application.Undo no_events = False ' your backup code ' ... no_events = True Target = tmp no_events = False End Sub
And events are no longer disabled, no more problem on that side in case of a crash.
eric
--
By continuously trying, we eventually succeed.
So the more it fails, the more chances we have that it works. (the Shadoks)
In addition to the thank you (yes, yes, it happens!!!), remember to mark as resolved. Thank you. -
Hello
Maybe like this:Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Selection.Cells.Count > 1 Then MsgBox "Error or multiple cell selection" ' the content is stored in the public variable "selected_address_value" selected_address_value = Range(Target.Address).Value history_content = "Sheet " & ActiveSheet.Name & " " & Target.Address & " : " & selected_address_value End Sub
Best regards