Solved

Excel 2010 VBA, ActiveX DLL and Ribbon

Posted on 2011-03-14
6
1,172 Views
Last Modified: 2012-05-11
Exploring the possibilities. I'm creating some classes in Class Modules for automating some of my routine development work (eg. create a combobox whcih is filled with data from a database via ADO). These will become ActiveX DLL via VB6. Now, in the new Ribbon, would it be possible to have a Ribbon tab from where I can launch a UserForm (from where I can select options like a wizard) just by adding the ActiveX Add In? ie. without also having to add an .xlam as well?

Can an expert point me in the right direction to learn how to do a Ribbon in Excel 2010 which will appear in Excel when the ActiveX DLL is Added In? And firing off the wizard?

Also re.2010. I got the RibbonX book by Robert Martin et al. Is the Excel 2010 a major change from 2007 > or are the 2007 Ribbon tutorials/tools still valid? (got VS2010 though newbie).
Thanks!

0
Comment
Question by:hindersaliva
  • 3
  • 2
6 Comments
 
LVL 85

Accepted Solution

by:
Rory Archibald earned 167 total points
ID: 35129713
Much of the 2007 ribbon stuff should be the same - Backstage is the major change for 2010 (and the xmlns value is different).
0
 
LVL 22

Assisted Solution

by:rspahitz
rspahitz earned 333 total points
ID: 35131031
One option for customizing in 2010 is to add a link to the Quick Access Toolbar (just above the menus, top left corner)
For this, you can right-click any item there (like the floppy disk/save) and pick "Customize the ribbon"
In the left panel, below "Customize Ribbon" is "Quick Access Toolbar"

Select that an in the middle panel, open the dropdown list at the top and select macros.

From the choices showing, pick the one you want and click the Add button to enable it.
Close this and the item will now be accessible from the toolbar.  no custom menus needed.

Of course, from there, you'll see that you can also add a custom tab and group to the ribbon and insert your pieces into the ribbon.

In your case, you'll probably need a macro that launches the form:

frmMyUserForm.Show

0
 

Author Comment

by:hindersaliva
ID: 35131332
Thanks rspahitz.

The way I need this to work is, I want the link to exist in the Excel 2010 Ribbon (and the macro to be accessible) ONLY when the user has the ActiveX DLL Add In installed. Is the QAT approach a possible path to that result?

Steps:
I have a blank workbook.
I install my ActiveX DLL Add In.
I now have a clickable button on a Ribbon tab.
I click the button and a UserForm/Wizard pops up.
I fill the boxes and click OK > (I know the way from there)
(Remember, its a blank workbook up to this point)

Thanks.
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 22

Assisted Solution

by:rspahitz
rspahitz earned 333 total points
ID: 35131384
I haven't worked with add-ins in years, so I'm not sure how to link it to the Excel 2010 application's configuration to add the tab/group you want.  I'll check with a few other experts for that.
0
 

Author Comment

by:hindersaliva
ID: 35137439
Update.
Had a dig around the RibbonX book and also the RibbonCustomizer tool/evaluation. I can see I'm looking for  a 'quick fix' for what is really a huge subject!
RibbonX book (Robert Martin et al) seems to have it covered from the ground up. So shall invest some time on it.
Thanks for the advice given above.

0
 
LVL 22

Expert Comment

by:rspahitz
ID: 35139585
Sounds like a good resource.  Good luck with the project.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

920 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

14 Experts available now in Live!

Get 1:1 Help Now