Home |
Search |
Today's Posts |
#1
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
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. |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
Dynamically setting worksheet tab colors | Excel Worksheet Functions | |||
Creating a Dynamically Linked Worksheet | Excel Worksheet Functions | |||
Dynamically linking subserviant worksheets to a master worksheet | Excel Worksheet Functions | |||
Dynamically Reference Offline Workbook/worksheet | Excel Discussion (Misc queries) | |||
Strange problem with a dynamically populated tabstrip on a worksheet | Excel Programming |