Use a userform in Excel 2007

Posted on 2012-09-14
Last Modified: 2013-11-10
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 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/ 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.


Question by:Terrygordon
    LVL 35

    Accepted Solution

    Yes, It is possible.
    You can create a VSTO Excel Add-in project and add a method.
    In the method you can call a winform and use the event handlers to interact with Excel in code.
    For sample code for interaction with Excel:

    Author Comment

    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.



    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Threat Intelligence Starter Resources

    Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

    This script will sweep a range of IP addresses (class c only, and report to a log the version of office installed. What it does: 1.)      Creates log file in the directory the script is run from (if it doesn't already exist) 2.)      Sweep…
    Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
    Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
    This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

    737 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

    18 Experts available now in Live!

    Get 1:1 Help Now