EXCEL Capture the value of a cell before change

Solved
electron43 -  
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

2 answers

  1. eriiic Posted messages 24581 Registration date   Status Contributor Last intervention   7 281
     
    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.
    1
  2. Frenchie83 Posted messages 2254 Status Member 339
     
    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
    0