Solved

Additional controls in VBA userforms?

Posted on 2011-03-08
18
1,150 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
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!

 

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

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

A2 = A1 That kind of cell reference is relative.  If you copy it from A2 to B2, then B2 will get this: B2 = B1 That's all fine and good, but if you then insert a new row above row 2, you'll find: A3 = A1 B3 = B1 This is intentional. …
What is a Form List Box? (skip if you know this) The forms List Box is the alternative to the ActiveX list box. If you are using excel 2007, you first make sure you have a developer tab (click the Orb)->"Excel Options"->Popular->"Show Developer tab…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

747 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

12 Experts available now in Live!

Get 1:1 Help Now