ODW

I'm not an expert on this subject, but I think the problem is that once you
assing the button, there's no reference to the class left over and VBA
cleans up the class when the sub ends. Look here

http://j-walk.com/ss/excel/tips/tip44.htm

Note that he uses an array as a module level variable. I think you would
need to use a global variable in a standard module since you're not working
on a userform (just guessing though). The array keeps that instance of the
class open so VBA doesn't clean it up for you.

If I'm right, then it seems like that last option button you add would work
properly, but all the others wouldn't. However, that might not be the case
because your class variable is Private in the sheet's class module. I've
seen some people use collections instead of arrays which also may be an
option for you.

--
Dick Kusleika
MVP - Excel
www.dicks-clicks.com
Post all replies to the newsgroup.

"onedaywhen" wrote in message
m...
I want to add OptionButtons to a worksheet at run-time and handle
their events in a class. However, when try to do this, not only do the
events fail to fire, but all my other objects and module-level
variables get wiped.

Perhaps I'm doing something wrong (but I have been able to do this in
the past with Userforms). Can anyone tell me how to dynamically add
optionbuttons to a worksheet then handle their events in a class?

Here's how to reproduce the behaviour I'm getting:

1. Open a new blank workbook.
2. From the Controls toolbar, add an optionbutton (need to do this to
reference the MS Forms 2.0 library?)
3. Open the Sheet1 code module and paste in the following code:

Option Explicit
Private m_strMyProperty As String
Private m_oClassy As Class1
Public Property Get MyProperty() As String
MyProperty = m_strMyProperty
End Property
Public Sub Worksheet_Initialize()
m_strMyProperty = "Chip Pearson"
End Sub
Public Sub AddOption()
Dim oOption As OLEObject
Set oOption = Me.OLEObjects.Add("Forms.OptionButton.1")
Set m_oClassy = New Class1
Set m_oClassy.OptionBtn = oOption.Object
End Sub

4. Add a class module (Class1) and paste in the following code:

Option Explicit
Private WithEvents m_oOption As MSForms.OptionButton
Public Property Set OptionBtn(NewOption As MSForms.OptionButton)
Set m_oOption = NewOption
End Property
Public Property Get OptionBtn() As MSForms.OptionButton
Set OptionBtn = m_oOption
End Property
Private Sub m_oOption_Change()
Stop
End Sub
Private Sub m_oOption_Click()
Stop
End Sub

5. Run the macro Worksheet_Initialize.
6. In the VBE Immediate Window, show the value of MyProperty to prove
it is still in scope e.g. ? Sheet1.MyProperty
7. Run the AddOption macro.
8. Hit the optionbutton and nothing happens (should encounter a Stop
and enter break mode).
9. Check the value of MyProperty to see that it is now null.

Thanks.