BEFORE SheetSelectionChange

[Brad]

Thanks for taking the time to read my question.

I'm using Excel 2003 and I want to capture changes to a cell's value. How do
I know with VBA that a cells value has changed / updated?

I tried SheetSelectionChange, but it only gives me the value of the new cell.

Basically what I'm trying to do is if a cells value has gone from "" to
anything, lock the cell so it can't be edited.

Thanks,

Brad

 

[Don Guillett]

Cells are, by default, locked IF the sheet is protected. use a
worksheet_change event if UNlocked

Private Sub Worksheet_Change(ByVal Target As Range)
If Target <> "" Then Target.Locked = True
End Sub

 

[Brad]

Here is what I have just just figured out.

Worksheet is protected.

Dim EnterCellVal As Variant
Dim EnterCellRow As Integer
Dim EnterCellCol As Integer


Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
If Cells(EnterCellRow, EnterCellCol).Value <> EnterCellVal Then
ActiveSheet.Unprotect
Cells(EnterCellRow, EnterCellCol).Locked = True
ActiveSheet.Protect
End If
End Sub

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target
As Range)
EnterCellVal = ActiveCell.Value
EnterCellRow = ActiveCell.Row
EnterCellCol = ActiveCell.Column
End Sub

Brad

 

[Don Guillett]

You are assuming that the cell is unlocked before you change it. Otherwise,
won't work. This will work also to place the existing value as public and
the worksheet_change will take over if a different value entered and it will
NOT fire with every calculation. ONLY when you change an UNlocked cell.

'============
Public oldvalue
Private Sub Workbook_SheetSelectionChange _
(ByVal Sh As Object, ByVal Target As Range)
oldvalue = Target.Value
End Sub
'=========
Private Sub Workbook_SheetChange _
(ByVal Sh As Object, ByVal Target As Range)
'MsgBox oldvalue
If Target <> oldvalue Then
ActiveSheet.Unprotect
Target.Locked = True
ActiveSheet.Protect
End If
End Sub
'============