LinkBack Thread Tools Search this Thread Display Modes
Prev Previous Post   Next Post Next
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 7
Default 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

 
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Need to store changing values from one cell Emmie Excel Discussion (Misc queries) 5 September 17th 06 08:10 PM
I want cell name vs cell reference in history tracking Lisa Excel Discussion (Misc queries) 0 July 18th 06 03:58 PM
Format cell color based on multiple cell values Zenaida Excel Discussion (Misc queries) 1 May 10th 06 07:31 PM
Store values of a dynamically changing cell Yogesh Excel Worksheet Functions 0 August 4th 05 06:40 PM
store cell contents and cell address for comparsion & suming Jim Whelchel Excel Programming 1 November 24th 04 04:03 PM


All times are GMT +1. The time now is 06:12 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"