Store Multiple Cell Values (Cell History)
 

Issue: My current code is not able to store multiple cell values

Current code logic (pseudo "code"):

1. In Workbook_SheetSelectionChange: Globally store value, address,
sheet name, and sheet code name of selected cell (only able to one
cell).

2. In Workbook_SheetChange:
If user makes an edit to one cell, vbYesNo window pops up.
If user proceeds (vbYes) then cell accepts change.
If user stops, vbNo, then change is discarded and original
value of cell is restored using
by letting cellvalue = globally stored cell value.
End If

Do I use an array to store multiple cells' information globally?

See code below (this is only hypothetical code for the sake of
brevity):
-----------
Public shName
Public shCName
Public CellValue
Public CellAddress
-----------
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal
Target As Excel.Range)

' Stall updating changes until all calculations are done
Application.ScreenUpdating = False
' Turn off events to avoid infinite loop within an event
Application.EnableEvents = False

' The trigger cell
MyCellAddress = ActiveSheet.Range("Apples").Address

' If the trigger cell is selected store its value, address _
' sheet name, and sheet code name it was on
If Target.Address = MyCellAddress Then
CellValue = Cells(Target.Row, Target.Column).Value
CellAddress = Target.Address
shName = Sh.Name
shCName = Sh.CodeName
Else
GoTo EnableMe
End If

' Enable for events and update of screen
EnableMe: Application.EnableEvents = True
Application.ScreenUpdating = True

End Sub
-----------
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As
Range)

' Stall updating changes until all calculations are done
Application.ScreenUpdating = False
' Turn off events to avoid infinite loop within an event
Application.EnableEvents = False

ApplesAddress = ActiveSheet.Range("Apples").Address

If Target.Address = Apples Then
ApplesAddress = ActiveSheet.Range("Apples").Address
' Looking for "y" entry in cell from user
With ActiveSheet.Range(ApplesAddress)
Set c = .Find("y", Lookat:=xlWhole, MatchCase:=False)
If Not c Is Nothing Then
a = MsgBox("Are you sure you want to edit this cell?",
vbYesNo)
If a = vbYes Then
' Protect sheet and allowing edit of cell
ActiveSheet.Protect DrawingObjects:=True, _
Contents:=True, Scenarios:=True
Else
' Place original value back in cell
ActiveSheet.Cells(Target.Row, Target.Column) =
CellValue
End If
Else
' Will add this code later. Password protect this cell.
End If
End With
End If

' Enable for events and update of screen
EnableMe: Application.EnableEvents = True
Application.ScreenUpdating = True

End Sub

Store Multiple Cell Values (Cell History)
 

Not sure what your ultimate goal is, but Excel can maintain a change history
for you.
The downside is you have to share the WB. Read the help to check on the
consequences.

Otherwise, the Range object has an .ID property (a string) that you could
may be store the previous value.

NickHK

"Han" wrote in message
oups.com...
Issue: My current code is not able to store multiple cell values

Current code logic (pseudo "code"):

1. In Workbook_SheetSelectionChange: Globally store value, address,
sheet name, and sheet code name of selected cell (only able to one
cell).

2. In Workbook_SheetChange:
If user makes an edit to one cell, vbYesNo window pops up.
If user proceeds (vbYes) then cell accepts change.
If user stops, vbNo, then change is discarded and original
value of cell is restored using
by letting cellvalue = globally stored cell value.
End If

Do I use an array to store multiple cells' information globally?

See code below (this is only hypothetical code for the sake of
brevity):
-----------
Public shName
Public shCName
Public CellValue
Public CellAddress
-----------
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal
Target As Excel.Range)

' Stall updating changes until all calculations are done
Application.ScreenUpdating = False
' Turn off events to avoid infinite loop within an event
Application.EnableEvents = False

' The trigger cell
MyCellAddress = ActiveSheet.Range("Apples").Address

' If the trigger cell is selected store its value, address _
' sheet name, and sheet code name it was on
If Target.Address = MyCellAddress Then
CellValue = Cells(Target.Row, Target.Column).Value
CellAddress = Target.Address
shName = Sh.Name
shCName = Sh.CodeName
Else
GoTo EnableMe
End If

' Enable for events and update of screen
EnableMe: Application.EnableEvents = True
Application.ScreenUpdating = True

End Sub
-----------
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As
Range)

' Stall updating changes until all calculations are done
Application.ScreenUpdating = False
' Turn off events to avoid infinite loop within an event
Application.EnableEvents = False

ApplesAddress = ActiveSheet.Range("Apples").Address

If Target.Address = Apples Then
ApplesAddress = ActiveSheet.Range("Apples").Address
' Looking for "y" entry in cell from user
With ActiveSheet.Range(ApplesAddress)
Set c = .Find("y", Lookat:=xlWhole, MatchCase:=False)
If Not c Is Nothing Then
a = MsgBox("Are you sure you want to edit this cell?",
vbYesNo)
If a = vbYes Then
' Protect sheet and allowing edit of cell
ActiveSheet.Protect DrawingObjects:=True, _
Contents:=True, Scenarios:=True
Else
' Place original value back in cell
ActiveSheet.Cells(Target.Row, Target.Column) =
CellValue
End If
Else
' Will add this code later. Password protect this cell.
End If
End With
End If

' Enable for events and update of screen
EnableMe: Application.EnableEvents = True
Application.ScreenUpdating = True

End Sub

Store Multiple Cell Values (Cell History)
 

I wasn't clear on what I was trying to get out of this post. Here is
my second attempt:

I would like to be able to have a user edit a cell, but if they change
their mind about the edit, the cell is restored to its original value.
BUT, I would like to have the macro built so that this idea applies
even when multiple cells are being edited simultaneously (I have the
code that does the first part for one cell--refer to previous post).

Example of what I am implying from the last statement:

User highlights A1 through C3 and pastes data in the cells, thereby
editing the selected cells simultaneously.

If the user change his or her mind about the edit, how do I put the old
values of A1 through C3 back into the cells? Keep in mind that I
cannot use the undo button because I have a macro that does things
after an edit, which does not allow for an undo.

Please advise.

-Han


NickHK wrote:
Not sure what your ultimate goal is, but Excel can maintain a change history
for you.
The downside is you have to share the WB. Read the help to check on the
consequences.

Otherwise, the Range object has an .ID property (a string) that you could
may be store the previous value.

NickHK

"Han" wrote in message
oups.com...
Issue: My current code is not able to store multiple cell values

Current code logic (pseudo "code"):

1. In Workbook_SheetSelectionChange: Globally store value, address,
sheet name, and sheet code name of selected cell (only able to one
cell).

2. In Workbook_SheetChange:
If user makes an edit to one cell, vbYesNo window pops up.
If user proceeds (vbYes) then cell accepts change.
If user stops, vbNo, then change is discarded and original
value of cell is restored using
by letting cellvalue = globally stored cell value.
End If

Do I use an array to store multiple cells' information globally?

See code below (this is only hypothetical code for the sake of
brevity):
-----------
Public shName
Public shCName
Public CellValue
Public CellAddress
-----------
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal
Target As Excel.Range)

' Stall updating changes until all calculations are done
Application.ScreenUpdating = False
' Turn off events to avoid infinite loop within an event
Application.EnableEvents = False

' The trigger cell
MyCellAddress = ActiveSheet.Range("Apples").Address

' If the trigger cell is selected store its value, address _
' sheet name, and sheet code name it was on
If Target.Address = MyCellAddress Then
CellValue = Cells(Target.Row, Target.Column).Value
CellAddress = Target.Address
shName = Sh.Name
shCName = Sh.CodeName
Else
GoTo EnableMe
End If

' Enable for events and update of screen
EnableMe: Application.EnableEvents = True
Application.ScreenUpdating = True

End Sub
-----------
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As
Range)

' Stall updating changes until all calculations are done
Application.ScreenUpdating = False
' Turn off events to avoid infinite loop within an event
Application.EnableEvents = False

ApplesAddress = ActiveSheet.Range("Apples").Address

If Target.Address = Apples Then
ApplesAddress = ActiveSheet.Range("Apples").Address
' Looking for "y" entry in cell from user
With ActiveSheet.Range(ApplesAddress)
Set c = .Find("y", Lookat:=xlWhole, MatchCase:=False)
If Not c Is Nothing Then
a = MsgBox("Are you sure you want to edit this cell?",
vbYesNo)
If a = vbYes Then
' Protect sheet and allowing edit of cell
ActiveSheet.Protect DrawingObjects:=True, _
Contents:=True, Scenarios:=True
Else
' Place original value back in cell
ActiveSheet.Cells(Target.Row, Target.Column) =
CellValue
End If
Else
' Will add this code later. Password protect this cell.
End If
End With
End If

' Enable for events and update of screen
EnableMe: Application.EnableEvents = True
Application.ScreenUpdating = True

End Sub