View Single Post
  #5   Report Post  
Posted to microsoft.public.excel.programming
No Name
 
Posts: n/a
Default How do I invoke a VB subroutine that exists in an Add-In?

This is for '97, don't know if it's quite the same.

Start the Visual Basic Editor by pressing Alt+F11.
Select Debug, Compile VBA projectname. In previous
versions of Excel the VBA-code was automatically compiled
when you saved the addin. This is no longer true, you have
to do it yourself before you save the addin.
If you want to lock the project from viewing you can
select Tools, Properties for VBA projectname. In this
dialog you activate Protection and check the option Lock
project for viewing. Fill in a password and click the OK-
button.
Activate Excel by pressing Alt+F11.
Select File, Properties..., Summary and fill inn
information for the fields Title and Comments. The title
will be the name that appears in the Add-Ins dialog (the
dialog displaying available add-ins), the comment will be
the description that appears when you select the addin in
the Add-Ins dialog. Click the OK-button to close the
Properties dialog.
Select File, Save as….
Change the option Filetype: to Microsoft Excel addin
(*.xla) (the last choice in the dropdown). The add-in must
contain at least one worksheet if this option is to be
displayed.
Click the Save-button to save the workbook as an addin.
The locking of the project will not take effect until you
close and re-open the workbook.
You can convert a workbook to an addin by changing the
property IsAddin to True for the ThisWorkbook-object. This
must be done from the Visual Basic Editor. When the
property is changed you can save the workbook by clicking
on the Save-toolbarbutton.

-----Original Message-----
It's not working. I deleted the old Add-In. I removed it

from the list in
the Add-In manager dialog box. I deleted my toolbar. I

closed and restarted
Excel. Then:

(1) I open the source .xls file.
(2) I save it as an Add-In.
(3) I close the source (saving it because it tells me it

changed) - Now I
only have my PERSONAL.XLS open but it is hidden
(4) I open the Add-In file.
(5) I create a new toolbar
(6) From the Customize pop-up I add a Custom-Button

(Categories: Macro,
Commands: Custom Button)
(7) I try to assign a macro to the button but there is no

pop-up... How do I
assign this button to the subroutine in Visual Basic?
(8) I go back several times to compile the code at

the .xls level and then
starting from scratch at the .xla level - Doesn't work.
(9) I try the new add-in after adding it to the list:

Nothing...

Any idea what I'm missing?

Thanks,
Tom
"Tom Ogilvy" wrote in message
...
Don't assign the macro to the button until you have

made the file an
addin.

--
Regards,
Tom Ogilvy

"Tom Doster" wrote in message
...
I've created an Excel Visual Basic subroutine that I

want to share as an
Add-In. How do I make it so that the user can run the

VB subroutine?

In the spreadsheet (.xls) source for the Add-In

(.xla) I have created a
new
toolbar with a button assigned to the main macro.

Then I compile the
Visual
Basic code and save the workbook as an Add-In file. I

then close all
files,
make the Add-In available through the Add-In manager,

open a new
spreadsheet, select some cells and then click on the

toolbar button that
I
linked to the Visual Basic subroutine. Trouble is:

The toolbar button
wants
to run VB script from the original spreadsheet, not

from the Add-In.

How do I tell the Excel toolbar (or menu, or

whatever) to run the Visual
Basic subroutine that exists in my Add-In file?

Can I do that directly, or do I have to have some

level of indirection:
The
toolbar calls a regular Visual Basic program that

invokes the subroutine
in
the Add-In?

I have Microsoft Excel 2002 on Windows XP Pro.

Thanks,
Tom Doster






.