Home |
Search |
Today's Posts |
#1
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
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 |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
Need to store changing values from one cell | Excel Discussion (Misc queries) | |||
I want cell name vs cell reference in history tracking | Excel Discussion (Misc queries) | |||
Format cell color based on multiple cell values | Excel Discussion (Misc queries) | |||
Store values of a dynamically changing cell | Excel Worksheet Functions | |||
store cell contents and cell address for comparsion & suming | Excel Programming |