Solved

Additional controls in VBA userforms?

Posted on 2011-03-08
18
1,160 Views
Last Modified: 2012-05-11
Hello everyone

I work a lot with userforms in Excel, but I find the controls quite boring.
Is it possible to install new controls?

Massimo
0
Comment
Question by:mscola
  • 8
  • 5
  • 5
18 Comments
 
LVL 22

Expert Comment

by:rspahitz
ID: 35074684
Yes!
In the toolbox, right-click and select Add Controls.  you'll see a lot of components that it pulls from the registry, some of which will work well with your forms (especially the Microsoft ones.)
0
 
LVL 22

Expert Comment

by:rspahitz
ID: 35074747
you can also get there from menu Tools | Additional Controls.
One that I like to add is the WebBrowser control, which uses the IE engine to load web pages.
If you've built your own OCXs, I think it supports those too, if they are registered.
0
 
LVL 45

Expert Comment

by:patrickab
ID: 35074798
You cab even have a spreadsheet within a UserForm
0
 

Author Comment

by:mscola
ID: 35074828
Thanks, I 've just had a look at them.
I do not know how to create OCXs.
Are there companies that are specialised with controls?
0
 
LVL 45

Assisted Solution

by:patrickab
patrickab earned 125 total points
ID: 35074832
You can even have a spreadsheet on a UserForm - see attached file
Ws-on-Userform-01.xls
0
 

Author Comment

by:mscola
ID: 35074881
Hi Patrick, doesn't work on Office 2010 :-(
"Could not load object .." - same message like when I try to open a form with a calendar control..

:-/
0
 
LVL 45

Expert Comment

by:patrickab
ID: 35074890
I only hav Xl2002 so I can't test it on XL2010
0
 
LVL 22

Expert Comment

by:rspahitz
ID: 35075009
There used to be companies specializing in OCXs but I haven't heard of any lately.  With DotNet, it's so much easier to create custom controls that maybe the market shrank.

Are you looking for anything in particular?  It could probably be built with VB6 and made available to Excel (although it's been a while since I cracked open my VB6 app.)

another possibility is to simply get the VB.Net 2010 Express edition, free from microsoft.com, then create an app that manages an Excel 2002 spreadsheet through the advanced forms.
0
 
LVL 45

Expert Comment

by:patrickab
ID: 35075042
Attached is a screenshot of a worksheet on a UserForm

Patrick
MWSnap-018-2011-03-08--23-15-02.jpg
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.

 

Author Comment

by:mscola
ID: 35075088
Hi Patrick

Looks nice. Too bad it doesn't work with the new Excel

rspahitz: well, I'm looking for controls that look "cool" - something to impress my boss. The vba excel userforms look very dated. Are you saying that .net controls will work with excel userforms?
0
 
LVL 22

Expert Comment

by:rspahitz
ID: 35075136
.net controls typically *replace* excel userforms.
Rather than have Excel launch the user form, you have the .net form launch and manage the excel sheet, similar to what Patrick is showing above.

The nice thing about VB.Net 2010 is that it's current so it has all the new looks to it.

However, another option, which will take some work, is to use a picture control to create cool pictures and intercept the mouse-over and mouse-move events to do cool things while the mouse is over the picture.  I guess it depends what you really want to do.  but I suggest looking at some of the controls in VB.Net to see what you think is nice, then maybe think about making a version of it for the old Excel 2002
0
 
LVL 22

Expert Comment

by:rspahitz
ID: 35075152
This link should let you download VB.Net:

http://www.microsoft.com/express/Downloads/#2010-Visual-Basic

I think you'll need to register it at some point with a free MSDN account.
0
 

Author Comment

by:mscola
ID: 35075180
Well.. that sounds interesting. Do you have any idea why Patrick's code doesn't run on the latest version of Excel?

Are you saying that, after having learned about Excel and Access, the next step would be to learn how to program in Visual Basic?
0
 
LVL 22

Accepted Solution

by:
rspahitz earned 125 total points
ID: 35075251
If Patrick's code doesn't work, it's probably because you don't have the proper references to it (or maybe it was not loaded on your Windows machine; if you could get the OCX (probably from the microsoft site somewhere) I suspect that it would work.

As for your other question, I find that amusing because I teach adult-ed class going from Excel to Access to Visual Basic.  I explain the progression and invite people to continue down that path if they like programming Excel forms.
Basically, Excel is an excellent data manipulation tool.  however, at some point it maxes out and it makes sense to go up the learning curve to Access and it's data manipulation.  When the forms get too cumbersome (as you're finding in Excel) then it's time to get the real power of VB.
0
 

Author Comment

by:mscola
ID: 35075301
Do you know the name of the control Patrick put in his form?

I used to work with Access and my current job allows me to work with Excel only, which can be very cumbersome at times. (not just the forms). I learned Java at school, so I guess I should not have difficulties getting started with VB? I have downloaded VB Express and will have a look at it when I'm at home.

In the meantime I will try and do my best with Excel.

Thanks a lot for your help
0
 
LVL 22

Expert Comment

by:rspahitz
ID: 35075350
Thanks ms.

I don't see patrick's control in my list, but I think I've run across it before somewhere.
We'll have to see if patrick reports back to supply you with the name of the dll from the Toolbox/References
0
 
LVL 45

Expert Comment

by:patrickab
ID: 35081567
>We'll have to see if patrick reports back to supply you with the name of the dll from the Toolbox/References

Microsoft Office Spreadsheet 10
0
 
LVL 22

Expert Comment

by:rspahitz
ID: 35084710
Interesting Patrick.  I don't have that in my control list or in my references.  Can you tell the dll name?
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

Sparklines have been introduced with Excel 2010 and are a useful tool for creating small in-cell charts, used for example in dashboards. Excel 2010 offers three different types of Sparklines: Line, Column and Win/Loss. What it does not offer is a…
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
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 …

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

16 Experts available now in Live!

Get 1:1 Help Now