• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2005
  • Last Modified:

Use a VB.net userform in Excel 2007

Hi All

I have a very complicated workbook that runs a business simulation using multiple sheets and lots of VBA modules. I am trying to make this look more professional by having the user interface based around userforms that access the worksheets (with Excel minimised and running only in the background).

Of course, this presents problems with the limited controls that can be used in VBA userforms. I have managed to create menubars and simulate things like mouseovers, etc. using lots of code, but it is still nowhere near as good as a VB.net userform.

I keep seeing in forums where experts have said "why not just create the userform in VB and export it to excel" (or words to that effect).

My question/s...is this even possible? Can it be done as an addin? Would I still be able to access cells, etc. in the main workbook (where all the calculations are done)? And, lastly, how do I do it?

One day, I will get around to programming the whole thing in VB, but there are literally thousands of formulas and interactions and excel is great at handling the complexity of this type of simulation.

In the meantime, any advice would be greatly appreciated.

Regards

Terry
0
Terrygordon
Asked:
Terrygordon
1 Solution
 
Miguel OzSoftware EngineerCommented:
Yes, It is possible.
How:
You can create a VSTO Excel Add-in project and add a vb.net method.
In the vb.net method you can call a winform and use the event handlers to interact with Excel in code.
Check:
http://msdn.microsoft.com/en-us/library/bb608614(v=vs.100)
http://blogs.msdn.com/b/eric_carter/archive/2008/04/21/vba-and-vsto-can-be-friends-part-i.aspx
For sample code for interaction with Excel:
http://support.microsoft.com/kb/302094
0
 
TerrygordonAuthor Commented:
Hi Mas

Thanks for the articles. I've had a quick look and it appears that what I am after may be possible, although it will take a while to fully test it out. Eric Carter's first article promises to demonstrate how to call VSTO from VBA, although the subsequent articles never seem to quite get there. I couldn't find anything beyond III in the series.

In any case, you have pointed me in the right direction.

Regards

Terry
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now