Advice on location of code for a VBA application 1

[Custom24]

Hi
I've written quite an involved VBA for excel application which meets the user requirements. The idea I went with was that for each job, the user will start a new workbook based on my template. The template file contains all the code, some sheets, and some command buttons.

The problem is that inevitably the application will need to be updated. With the stuff I've done in the past, this was not so much of an issue because there was only one workbook rather than a template spawning multiple copies of itself.

One option is making the code in the main workbook refer to another workbook which will store all the user infromation without any code. The problem with this is that there are some command buttons to guide the user thru the process and I don't really want them to have to keep switching back to the main workbook to click these. But the advantage is that any changes I make to the main workbook (on a network) will be immediately seen by the users.

Another option would be converting my application to an excel add-in. I don't know much about how to do this, but the problem (I think) is that it would need to be distributed in the same way as a VB6 .exe?

Has anyone got any other options? Or some advice on which way to go?

Thanks for listening
Mark

 

[SkipVought]

hi, Custom24,

Here's one possibility...

Configure your main workbook so that it cannot be saved. This will mean creating a new CommandBar with a customized main menu having only the controls that you want the user to have in your workbook. The user launches the application and employs it to create a new workbook based on the events of their interaction with your program. When their workbook is fully created and configured, then the final act of your program might be to simply close, leaving the user to embark on their new workbook experience.

Skip,
metzgsk@voughtaircraft.com

 

[Custom24]

Thanks - I've never created a menu item in XL before. Does this mean I need to create a VBA add-in? Or is there just an object in the XL object library to do this?

The other issue is that the application does not just configure a workbook once - the user has to keep interacting with the workbook over a period of weeks as new bits of data are gathered.

I guess that I could replace the command buttons in the active code workbook with menu items if I knew how to get them into XL...

Thanks again
Mark

 

[SkipVought]

So couldn't your program, spawn a workbook that links to the Main Workbook. The spawned template could have a toolbar that would engage the user inteerface for the updates that are required or the interface could fire up on certain workbook or worksheet events. The key would be a reference to your Main in the user's workbook.

I have never created an addin. You might want to check out

http://www.j-walk.com.

Walkenbach is a real Excel expert and has written several books on Excel and VBA. He has some advice about creating addins. This approch does not have to be an addin, but maybe it shoud be.

The new CommandBar approch employs MS Office level controls. I would suggest consulting online help and "playing around" with some of the examples, macro record some of your adventures and go from there. Skip,
metzgsk@voughtaircraft.com