You gave a better intro to class'ing control events than I could have
written, had you not said otherwise I would assume you already an expert!
You hinted at not being quite sure about how to know which control is
triggering the event, I'm no expert on classes (certainly not in the company
of Jamie C) but maybe this nonsense example will demonstrate a few things:
Manually create three checkboxes on Sheet1 with linked-cells A1, A2 & A3
(don't cover cell E10)
Code in a class named clsCheckBoxEvnts and normal module as indicated -
''start Class named clsCheckBoxEvnts
Option Explicit
Public WithEvents ChBox As MSForms.CheckBox
Private appVer As Long
Private sTrick As String
Private Sub ChBox_Change()
Dim rng As Range, cx As Long, s As String, i As Long
With ChBox
Set rng = .Parent.Range(.LinkedCell) _
.Offset(0, 1).Resize(1, 4)
If .Value Then
.Caption = .Index & " " & MyString
cx = .Index + 24
Else
.Caption = .Name
cx = xlNone
End If
If gcVars.some_var = 8 Then xl97fix
rng.Interior.ColorIndex = cx
For i = 1 To UBound(gaCBs)
If gaCBs(i).ChBox.Value Then
'compare ".Caption = MyString" above and next line
s = s & gaCBs(i).MyString & " "
End If
Next
If s = "" Then s = gcVars.MyString
.Parent.Range("e10") = s
End With
End Sub
Public Property Let MyString(str As String)
sTrick = str
End Property
Property Get MyString() As String
MyString = sTrick
End Property
Public Property Let some_var(n As Long)
appVer = n
End Property
Property Get some_var() As Long
some_var = appVer
End Property
Private Sub xl97fix()
'in xl97, if checkbox has focus can error
'if changing cell formats (but not values), very odd!
On Error GoTo done
'prevent any selection change events
Application.EnableEvents = False
If Intersect(Windows(1).VisibleRange, ActiveCell) Is Nothing Then
Windows(1).VisibleRange(1, 1).Activate
Else
ActiveCell.Activate
End If
done:
Application.EnableEvents = True
End Sub
Private Sub Class_Terminate()
ChBox.Enabled = False
End Sub
''end code in clsCheckBoxEvnts
'''''''''''''''''''''''''''''''''''''''''''''''
''start code in normal module
Option Explicit
Public gaCBs() As New clsCheckBoxEvnts
Public gcVars As New clsCheckBoxEvnts
Sub Setup()
'called by [say] wb-activate
Dim i As Long
Dim obOLE As OLEObject
Dim va
va = Array("Rabbit", "Hat", "Magic")
For Each obOLE In Worksheets("Sheet1").OLEObjects
If TypeOf obOLE.Object Is MSForms.CheckBox Then
i = i + 1
obOLE.Object.Enabled = True
ReDim Preserve gaCBs(1 To i)
Set gaCBs(i).ChBox = obOLE.Object
gaCBs(i).MyVar = va(i - 1)
End If
Next
gcVars.MyString = "No tricks"
gcVars.some_var = CLng(Val(Application.Version))
setCBoxes False
End Sub
Private Sub setCBoxes(bVal As Boolean)
Dim ob As Object, i As Long
For i = 1 To UBound(gaCBs)
gaCBs(i).ChBox.Value = bVal
Next
End Sub
Sub Clearup()
'called by say wb deactivate
Erase gaCBs
End Sub
''end code in normal module
In real life code I can't imagine ever using the "array" method of holding
the controls with an unknown qty using redim preserve. Typically the array
method is used fully dimensioned to hold a known number of similar controls
on a userform. Use the "collection" method (see previous link to Stephen
Bullen's example).
Also, wouldn't store the app-version variable this way.
As you've had so many problems with controls in xl97 look at the "xl97fix"
routine, do you find you need it?
Regards,
Peter T
"Matt Jensen" wrote in message
...
Hey Peter
Thanks for this.
Been thinking about this some more, think my understanding of classes is
slowly improving - effectively, one is 'class'ifying a group of elements
with the intent to treat, particularly events, them all as doing the same
thing - is this right?
Part of my inability to understand classes I think is due to the fact that
I
thought that surely when say a control toolbox checkbox event occurred
that
you could call a procedure and the procedure would be able to know, among
other things, the name of the checkbox calling it eg. by using 'this.' or
'me.' but from what I can tell this is not the case with VBA?
However, I guess I can see the advantage of "classing" elements as part of
the same class and associating particular events with this class, and
hence
modularising application objects and keeping them distinctly separate from
others.
Is this understanding right?
Matt
"Peter T" <peter_t@discussions wrote in message
...
"Matt Jensen" wrote in message
...
Hey Peter
Re checkbox events, I don't really use them, I've just got them linked
to
a
corresponding row/column in a separate sheet which stores their value,
no
need for events luckily. Apart from the fact that I'm not competent
enough
to create classes yet. I don't really understand them actually - I do
but
I
haven't seen an use for them yet, although if I understood them more
maybe
I
would use them more often...!
Sure, if the value of the linked cell is used only in worksheet formulas
you
wouldn't need events.
Stephen Bullen recently posted an example of trapping events of similar
controls in a class he
Subject: "running same code with multiple controls" Jan 2005
http://tinyurl.com/4jp2v
Stephen's example was with Labels but easily changed to Checkboxes. In
the
class click event could do something like:
Set rng = Range(With-events-variable-name-of-checkbox.LinkedCell)
and then do all sorts of things.
The file is only 550KB or so at the moment after I rebuilt in 97 (was
1.5MB), although it seems to increase in file size with every save,
even
it
you just open then save then close...!
This could be due to reworking your code. Rob Bovey's Code Cleaner is
universally recommended:
http://www.appspro.com/
On a slightly different but related note, how do prevent printing of
cell
data - is there a row.PrintObject = False method or something, or do
I
have
to hide/change colours etc. or something?
I posted some comments yesterday to another part of the thread.
Regards,
Peter T