Greetings!
I'm just exploring ways to encapsulate information on a spreadsheet.
If there's a way, I'd be interested even if it doesn't involve a userform.

Thanks/Cheers!

build your own oblects using class modules. depends on what it is that you
want to do of course


"tbd" wrote:

Greetings!
I'm just exploring ways to encapsulate information on a spreadsheet.
If there's a way, I'd be interested even if it doesn't involve a userform.

Thanks/Cheers!

Hi Patrick,
I like the sound of this... Tried exporting a Class object to .cls
file, and inserted it using \insert\object\fromfile. A floating icon with
filename shows-up (it's not embedded in cell, maybe OK), but I'd like to see
some sort of GUI - like a userform. VBA would need to position about 30 of
these, then user edits inside each one...

Maybe it's time to switch to VB, and embed Excel objects in a VB GUI.

Thanks/Cheers!

"Patrick Molloy" wrote:

build your own oblects using class modules. depends on what it is that you
want to do of course


"tbd" wrote:

Greetings!
I'm just exploring ways to encapsulate information on a spreadsheet.
If there's a way, I'd be interested even if it doesn't involve a userform.

Thanks/Cheers!

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














"tbd" wrote:

Hi Patrick,
I like the sound of this... Tried exporting a Class object to .cls
file, and inserted it using \insert\object\fromfile. A floating icon with
filename shows-up (it's not embedded in cell, maybe OK), but I'd like to see
some sort of GUI - like a userform. VBA would need to position about 30 of
these, then user edits inside each one...

Maybe it's time to switch to VB, and embed Excel objects in a VB GUI.

Thanks/Cheers!

"Patrick Molloy" wrote:

build your own oblects using class modules. depends on what it is that you
want to do of course


"tbd" wrote:

Greetings!
I'm just exploring ways to encapsulate information on a spreadsheet.
If there's a way, I'd be interested even if it doesn't involve a userform.

Thanks/Cheers!

Patrick, THANKS!!!
Really appreciate the example code! Probably never would have implemented
this on my own, but I can see the value of having this tool, AWESOME! - will
keep it in a safe place.

BTW, sorry for slow reply, something at MS changed(?) in the hyperlink chain
I used to use to get here - it took a while to google a known post, and
re-link.
--
The Cobra
[Footnote:] A few Cobras in your home will soon clear it of Rats and Mice.
Of course, you will still have the Cobras. (Will Cuppy
http://en.wikiquote.org/wiki/Will_Cuppy)


"Patrick Molloy" wrote:

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














"tbd" wrote:

Hi Patrick,
I like the sound of this... Tried exporting a Class object to .cls
file, and inserted it using \insert\object\fromfile. A floating icon with
filename shows-up (it's not embedded in cell, maybe OK), but I'd like to see
some sort of GUI - like a userform. VBA would need to position about 30 of
these, then user edits inside each one...

Maybe it's time to switch to VB, and embed Excel objects in a VB GUI.

Thanks/Cheers!

"Patrick Molloy" wrote:

build your own oblects using class modules. depends on what it is that you
want to do of course


"tbd" wrote:

Greetings!
I'm just exploring ways to encapsulate information on a spreadsheet.
If there's a way, I'd be interested even if it doesn't involve a userform.

Thanks/Cheers!