Can't dynamically add withevents OptionButton to worksheet

[onedaywhen]

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.

 

[Dick Kusleika]

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.

 

[onedaywhen]

Dick,
Thanks for your reply

I think the problem is that once you
assign the button, there's no reference to the class left over and VBA
cleans up the class when the sub ends.

In the Sheet1 code module I have declared a module level variable
(i.e. a reference to the class) with this line:

Private m_oClassy As Class1

In the AddOption sub I instantiate it but don't set it to Nothing,
therefore it should survive the scope of AddOption and be retained.
However, if I set a break point in the Initialize sub, re-run
Initialize and, while in break mode, type this in the immediate window

? m_oClassy Is Nothing

it returns true. I don't understand why VBA has cleaned this up.

Did you mean that the oOption variable was local to AddOption and this
was causing the m_oOption variable in the class to be cleaned up? I
don't think this *should* be the case but to check I moved the
declaration of oOption to General Declarations in the Sheet1 code
module and it made no difference.

In any case, I don't understand why the module level variable
m_strMyProperty gets trashed. m_strMyProperty isn't reference at all
by AddOption. m_strMyProperty survives the scope of the Initialize sub
but gets trashed after AddOption. Even when I move m_strMyProperty to
a standard module and make it public its value still gets wiped by
AddOption.

I even tried putting the various variables and objects into a public
collection (declared with the New keyword then without and
instantiating the new collection separate from the declaration) in a
standard module. Same results i.e. everything survives scope until the
AddOption is run and everything related and non-related gets wiped and
I'm left with an empty collection.

It would be good to set breakpoints in AddOption and test exactly when
the module level variables get wiped but try it on any line and I get
a dialog saying, 'Can't enter break mode at this time' with the Debug
button disabled. So I try something else, suggested in KB article
155051, and make every other line

Debug.Print m_oClassy Is Nothing

The results are False, False, False, so all is fine during run-time.
But check it immediately after run-time and it's now True. Trashed.

All very suspicious! So I conclude it's not a scope issue.

 

[Dick Kusleika]

ODW

I have to agree with your conclusions. I think I tested every possible way
to do this, and I get the same results as you in all cases. I took all the
code out of the sheet module and put it in a standard module, change the
Class1 variable to Public and just tried to simplify everything as much as
possible. So I conclude that this is a bug and if you search google for
"Can't enter break mode at this time" you'll see some similar bugs, but
nothing like this - although I think they're related in some way.

Next, I decided to skip the optionbutton creation, create one manually and
then assign it to the class. That seemingly did not work either. Here's
the code I used.

In as standard module

Option Explicit

Public OptClass() As New Class1

Sub PutOptInClass()

Dim oleo As OLEObject
Dim i As Long

i = 0
For Each oleo In Sheet1.OLEObjects
If TypeOf olebject Is MSForms.OptionButton Then
i = i + 1
ReDim Preserve OptClass(1 To i)
Set OptClass(i).m_oOption = olebject
End If
Next oleo

End Sub

In the class module

Option Explicit

Public WithEvents m_oOption As MSForms.OptionButton

Private Sub m_oOption_Click()
MsgBox "me"
End Sub

When I click on the option button, nothing happens. I added a new
optionbutton onto the sheet (so there are two) and reran the procedure.
Works like a charm. I guess I don't understand the difference between the
Change event and the Click event for option buttons. It appears that they
are the same and you need two option buttons for either to work.

Back to creating them. I deleted the two option buttons and ran this sub
from the same standard module.

Sub MakeNewOpts()

Dim oleo As OLEObject
Dim i As Long

For i = 1 To 2
Set oleo = Sheet1.OLEObjects.Add("Forms.OptionButton.1")

oleo.Top = oleo.Top + (i * 40)
ReDim Preserve OptClass(1 To i)
Set OptClass(i).m_oOption = olebject
Next i

End Sub

Nothing fired - pretty much the same result as you got included the
inability to step through the code. My new theory is that the OLEObjects
that get added programmatically aren't fully mature in the eyes of the code
engine (whatever the hell that means) and they are not getting added to the
class. And that same condition is confusing VBA to the point that it won't
let you enter break mode.

Here's the workaround attempt - in the standard module after deleting all
option buttons from the sheet

Sub WorkAround1()

Dim oleo As OLEObject
Dim i As Long

For i = 1 To 2
Set oleo = Sheet1.OLEObjects.Add("Forms.Optionbutton.1")
oleo.Top = oleo.Top + (i * 40)
Next i

WorkAround2

End Sub

Sub WorkAround2()

Dim coleo As OLEObject
Dim j As Long
j = 0
For Each coleo In Sheet1.OLEObjects
If TypeOf colebject Is MSForms.OptionButton Then
j = j + 1
ReDim Preserve OptClass(1 To j)
Set OptClass(j).m_oOption = colebject
End If
Next coleo

End Sub

No luck. When I create the option buttons programmatically, the do not
appear on the screen in Excel. I have to scroll down and then scroll up to
see them. I think this is a side effect of the same issue that's causing
your problems, but that's just a guess (and probably not very useful
information.)

OK, I'm done. That's a really long post to tell you what you already knew.
I think your options are to use two subs: one for option button creation and
one for adding them to the class, or to figure out a way to make Excel
"recognize" those OLEObject when you create them.

All right, I said I was done, but here's one more thing. Keep WorkAround2
the same and change WorkAround1 to this

Sub WorkAround1()

Dim oleo As OLEObject
Dim i As Long

For i = 1 To 2
Set oleo = Sheet1.OLEObjects.Add("Forms.Optionbutton.1")
oleo.Top = oleo.Top + (i * 40)
Next i

Application.OnTime Now + TimeValue("00:00:03"), "WorkAround2"

End Sub

and it works. Now I have a headache. Let me know what you end up doing or
if you learn anything new.

 

[onedaywhen]

Dick,
First off, thank you so much for spending the considerable time and
effort on this issue for me, definitely above and beyond. Second, you
got a result where I failed and, although I'm not surprised - you MVPs
are something else - I certainly am impressed.

In answer to your questions, I will end up using this approach in my
solution if I can successfully change the delay to
TimeValue("00:00:01") - I need to test for timing issues - and decide
that even a one second delay is acceptable to my application.

Did I learn something new? I was about to say, "Not really..." but I
quickly checked myself. I knew how to add optionbuttons
programmatically and I knew I could subclass (is that the right
expression?) optionbuttons that had been added manually. But I was
about a million miles away from making the leap required to try
connecting the two separate routines with Application.OnTime. Thanks
to you, though, next time I may make that leap.

Thanks again.

 

[Dick Kusleika]

ODW

I asked around about this and it sounds like this is a known issue, although
maybe not fully understood. Note the difference in the right-click menu
when you create an option button programmatically vs. manually. I've never
noticed it before, but Excel must treat those two option buttons
differently.

Another suggestion I got was to forget the class module and just create the
event in the sheet's module as you create the option button. Here's the
example from John Green

Public Sub AddOption()
Dim oOption As OLEObject
Dim oCodeModule As CodeModule
Dim lLine As Long

Set oOption = Sheet1.OLEObjects.Add("Forms.OptionButton.1")
oOption.Name = "Option"
Set oCodeModule =
ThisWorkbook.VBProject.VBComponents(Me.CodeName).CodeModule

With oCodeModule
lLine = .CreateEventProc("Change", "Option")
.ReplaceLine lLine + 1, " Stop"
End With

Application.Visible = False
Application.Visible = True
End Sub

Also, Rob Bovey gave an example that used the OnTime, but he didn't have any
TimeValue function, he just used now. That's enough apparently, so if you
decide to stick with the OnTime, just use Now instead of Now + TimeValue(...


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

 

[onedaywhen]

Thanks for the update, Dick. I prefer to use a class module because I
generally use an OOP approach. Indeed, in this case the OptionButton
is just one property of the class. And it makes me a happy developer
if I can actually program according to my preferred philosophies. I
believe that when I start adding code to modules at run-time (rather
than as a once off exercise) I'm doing something wrong - it's not
natural, same as that CallByName function… Rob's hint about OnTime may
make me a happy developer!

Thanks again, Dick.