Solved

Excel 2007: Events For Activex Controls Embedded in Microsoft Forms 2.0 Frame

Posted on 2013-02-06
7
1,330 Views
1 Endorsement
Last Modified: 2013-02-07
I have placed a Microsoft Forms 2.0 Frame on an Excel 2007 worksheet.  I right-clicked the frame and added two optionbuttons to the frame.  I can see the events for the frame, but I can't figure out how to see the events for each of the embedded optionbuttons.  I want to trigger an event when either of the embedded optionbuttons are clicked.

What am I missing?

Many Thanks,

Bill Vallance
1
Comment
Question by:bvallanc
  • 3
  • 3
7 Comments
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 38862264
If you go to the sheet module for that worksheet, you will be able to leverage the events.  Select "OptionButton1" or whatever in the left-hand dropdown over the code pane, and then select your event from the right-hand dropdown.

I must say, though, that I advise against embedding ActiveX in a worksheet.  They simply do not behave well in worksheets.
0
 

Author Comment

by:bvallanc
ID: 38862421
I just opened a brand new Excel 2007 worbook.  On Sheet1 I placed a Microsoft Forms 2.0 Frame.  I right-cliked the frame and added two (2) optionbuttons - OptionButton1 and OptionButton2.  I opened the Visual Basic editor, opened the Sheet1 code module, and looked for the OptionButton1 and OptionButton2 in the left-hand listing of controls that have events.  Neither OptionButton1 or OptionButton2 is listed.  The Frame is listed, but neither of the optionbuttons.

Do I need to configure some other setting in Excel for these embedded optionbuttons to become visible in their sheet code module?

Thanks,

Bill Vallance
0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 38863723
Bill,

Your question indicated that you placed ActiveX option buttons in the frame, and I based my response on that.

If what you really have are Forms option buttons, then my advice is not applicable, and the method I described will not expose their events.

(That said, my warning about ActiveX controls in a worksheet still applies.  I dislike using controls generally, but if one must use controls, the Forms controls are generally more well-behaved than the ActiveX controls.)

Patrick
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:bvallanc
ID: 38863887
Thanks for your response, Patrick (I assume I have the right name?).  I was probably presumtuous in using the work "Activex" in the title of this question.  I assumed that the Microsoft Forms 2.0 Frame is an activex control because I accessed it from the Excel 2007 activex controls toolbox (by clicking the "More" buttom).  At this point I don't know whether the Microsoft Forms 2.0 Frame control is an activex or forms control.

Whatever type of control the Microsoft Forms 2.0 Frame may be, the behavior I described in my previous post (38862421) is what I am experiencing.  Googling this problem turns up several developers experiencing this same problem but no answer is ever given.  It appears that this frame control was never designed to handle embedded control events, just VBA status polling of the ActiveControl status of the embedded controls.

Thoughts?

Bill Vallance
0
 
LVL 92

Assisted Solution

by:Patrick Matthews
Patrick Matthews earned 125 total points
ID: 38864541
Yes, that's the right name :)

The Forms controls don't offer the same level of programmability that ActiveX controls offer.

Now, there is a virtue in that simplicity: they are far more stable when embedded in a worksheet than the ActiveX controls are.

If you use Forms option buttons, you can get a Click event by right clicking the control and selecting 'assign macro', but in terms of getting other events in your scenario I am at a loss.

You may want to click Request Attention and ask the Mods to send alerts to additional Experts.
0
 
LVL 85

Accepted Solution

by:
Rory Archibald earned 125 total points
ID: 38865357
It looks to me as though you have an ActiveX Frame but Forms optionbuttons. The Frame wil therefore expose events, but the optionbuttons won't - they can only be assigned macros as Patrick said.
0
 

Author Closing Comment

by:bvallanc
ID: 38866693
Thanks to both of you for your time and responses.  Rorya, that's exactly what it feels like to me, too.  I cut my losses with the Microsoft Forms 2.0 Frame and deleted it.  I used standard Activex optionbuttons, GroupNamed them, and placed a rectangular shape around them.  That construct works just like its supposed to.
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now