here's a very simple example - you sound knowledgable enough ....but let me
know
there are three parts:
(1) so in a standard module put this code:
Option Explicit
Public col As Collection
Public Function GetCellClass(targetaddr As String) As cellstuff
On Error Resume Next
If col Is Nothing Then
Set col = New Collection
Else
Set GetCellClass = col.Item(targetaddr)
End If
If GetCellClass Is Nothing Then
Set GetCellClass = New cellstuff
col.Add GetCellClass, targetaddr
End If
End Function
(2)in a CLASS module, named cellstuff, put this code
Option Explicit
Public PrevValue As String
Public Comment As String
Public User As String
Public CurrentValue As String
(3)and finally, in a sheet's code page, this:
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim celldata As cellstuff
Set celldata = GetCellClass(Target.Address(False, False))
With celldata
.PrevValue = .CurrentValue
.CurrentValue = Target.Value
.User = "me2"
End With
End Sub
so how does it work
When you enter a value into a cell, the chanmge event fires, this gets a
cellstuff object
it copies the current value in cellstuff to teh Privious value. pretty
simple hey? but there's no way to do this built in !
Anyway, the getcellstuff function checks to see if the collection of objects
exists. If it doesn't, create it. The it recalls the appropriate object - i
use the cell address as the key, as it be unique to any cell (or excel would
break).
if the object doesn't exist, its created, then the celldata object is handed
back to the call in the change event.
it should be pretty easy to push teh data into a userform abd vice-versa...I
just wanted to give you the idea
Hit YES if this helps
cheers
Patrick
