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

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" <matrixboarder@gmail.com> wrote in message
news:1156288944.596913.283020@b28g2000cwb.googlegroups.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
>