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

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

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

To assign it manually, just type in the name
MyAddin.xla!MyMacro

in code

CommandBars("Worksheet Menu Bar").Controls("Tools") _
.Controls("Mybutton").OnAction = "MyAddin.xla!MyMacro"

--
Regards,
Tom Ogilvy

"Tom Doster" wrote in 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

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






.

The toolbar button now works! Thanks! I used the process below to make this
work. I do have one more question: When I give the .xla file to my user will
he also have to assign the macro to the toolbar as I did in step 12 below,
or is that now set in the .xla somehow?

(1) Start from scratch. No Add-In. No toolbar. Have only source .xls for
Add-In.
(2) Open source .xls. Visual Basic project is already compiled.
(3) Create custom toolbar and add custom macro button. Don't assign anything
to the button.
(4) Save .xls.
(5) Save As .xla
(6) Close .xls (don't save changes when asked)
(7) Toolbar still exists.
(8) Can't add an Add-In - is disabled.
(9) Create a blank spreadsheet. Now Add-In is enabled.
(10) Add the Add-In
(11) Type some text, select the text and then Click on macro button on
toolbar.
(12) In 'Assign Macro' dialog type InsertFilesAddIn.xla!InsertFilesSub (my
Add-In routine)
(13) Function runs!!!

Tom
"Tom Doster" wrote in 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

Probably not.

It would be better to build the toolbar with code in the open event of the
addin. (then you also make the assignment (using code) and you will be sure
the assignment is to your addin

Here is an article about creating commandbars with code:
http://msdn.microsoft.com/library/techart/ofcmdbar.htm


Here is another article or two you might find useful:
http://msdn.microsoft.com/library/ba...n_addins97.htm

http://www.microsoft.com/exceldev/tips/addins.htm

Here is another article or two you might find useful:
http://msdn.microsoft.com/library/ba...n_addins97.htm
http://msdn.microsoft.com/library/of...rcreatingexcel...
These are about distributing applications

--
Regards,
Tom Ogilvy


"Tom Doster" wrote in message
...
The toolbar button now works! Thanks! I used the process below to make
this
work. I do have one more question: When I give the .xla file to my user
will
he also have to assign the macro to the toolbar as I did in step 12 below,
or is that now set in the .xla somehow?

(1) Start from scratch. No Add-In. No toolbar. Have only source .xls for
Add-In.
(2) Open source .xls. Visual Basic project is already compiled.
(3) Create custom toolbar and add custom macro button. Don't assign
anything
to the button.
(4) Save .xls.
(5) Save As .xla
(6) Close .xls (don't save changes when asked)
(7) Toolbar still exists.
(8) Can't add an Add-In - is disabled.
(9) Create a blank spreadsheet. Now Add-In is enabled.
(10) Add the Add-In
(11) Type some text, select the text and then Click on macro button on
toolbar.
(12) In 'Assign Macro' dialog type InsertFilesAddIn.xla!InsertFilesSub
(my
Add-In routine)
(13) Function runs!!!

Tom
"Tom Doster" wrote in 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

Thanks Tom O.,
I got the example toolbar code for Sub NewToolBar() working, but only when I
run it manually. I'm having in trouble in general getting anything to happen
on workbook open.

I've tried the following function in a module - nothing.
Private Sub Workbook_Open()
MsgBox "Workbook is Now Open!"
End Sub

And I've tried events. I've got one class module called EventClassModule
containing:
Public WithEvents App As Application
Private Sub App_SheetActivate(ByVal Sh As Object)
MsgBox "Sheet Activated"
End Sub
Private Sub App_WorkbookOpen(ByVal Wb As Workbook)
MsgBox "Workbook Opened"
End Sub

And I've got a module containing:
Dim X As New EventClassModule
Sub InitializeApp()
Set X.App = Application
End Sub

After running the InitializeApp() manually, App_SheetActivate() works!
That's good. If I save the .xls and open it however App_WorkbookOpen()
doesn't seem to run. I would think that somehow I have to run
InitializeApp() first thing when the workbook opens. But if I could do that
then why would I care about any of this event stuff? Why wouldn't I just
call the NewToolbar() subroutine and be done with it.

I read the through the documents you suggested but must be missing
something. Can you help?

Tom
"Tom Doster" wrote in 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

The Workbook_Open routine should be behind the ThisWorkbook module.

(or rename it to Auto_open and keep it in a General module.)

I haven't followed the thread, but I don't think you need an application event
for your purposes.

Tom Doster wrote:

Thanks Tom O.,
I got the example toolbar code for Sub NewToolBar() working, but only when I
run it manually. I'm having in trouble in general getting anything to happen
on workbook open.

I've tried the following function in a module - nothing.
Private Sub Workbook_Open()
MsgBox "Workbook is Now Open!"
End Sub

And I've tried events. I've got one class module called EventClassModule
containing:
Public WithEvents App As Application
Private Sub App_SheetActivate(ByVal Sh As Object)
MsgBox "Sheet Activated"
End Sub
Private Sub App_WorkbookOpen(ByVal Wb As Workbook)
MsgBox "Workbook Opened"
End Sub

And I've got a module containing:
Dim X As New EventClassModule
Sub InitializeApp()
Set X.App = Application
End Sub

After running the InitializeApp() manually, App_SheetActivate() works!
That's good. If I save the .xls and open it however App_WorkbookOpen()
doesn't seem to run. I would think that somehow I have to run
InitializeApp() first thing when the workbook opens. But if I could do that
then why would I care about any of this event stuff? Why wouldn't I just
call the NewToolbar() subroutine and be done with it.

I read the through the documents you suggested but must be missing
something. Can you help?

Tom
"Tom Doster" wrote in 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







--

Dave Peterson

I don't believe I suggested application level events (or see any reason to
have them), so I don't know where he is going with that.

--
Regards,
Tom Ogilvy

"Dave Peterson" wrote in message
...
The Workbook_Open routine should be behind the ThisWorkbook module.

(or rename it to Auto_open and keep it in a General module.)

I haven't followed the thread, but I don't think you need an application
event
for your purposes.

Tom Doster wrote:

Thanks Tom O.,
I got the example toolbar code for Sub NewToolBar() working, but only
when I
run it manually. I'm having in trouble in general getting anything to
happen
on workbook open.

I've tried the following function in a module - nothing.
Private Sub Workbook_Open()
MsgBox "Workbook is Now Open!"
End Sub

And I've tried events. I've got one class module called EventClassModule
containing:
Public WithEvents App As Application
Private Sub App_SheetActivate(ByVal Sh As Object)
MsgBox "Sheet Activated"
End Sub
Private Sub App_WorkbookOpen(ByVal Wb As Workbook)
MsgBox "Workbook Opened"
End Sub

And I've got a module containing:
Dim X As New EventClassModule
Sub InitializeApp()
Set X.App = Application
End Sub

After running the InitializeApp() manually, App_SheetActivate() works!
That's good. If I save the .xls and open it however App_WorkbookOpen()
doesn't seem to run. I would think that somehow I have to run
InitializeApp() first thing when the workbook opens. But if I could do
that
then why would I care about any of this event stuff? Why wouldn't I just
call the NewToolbar() subroutine and be done with it.

I read the through the documents you suggested but must be missing
something. Can you help?

Tom
"Tom Doster" wrote in 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







--

Dave Peterson

I skimmed through the thread and didn't see anything like that suggested--It
kind of came as a surprise to see it in the OP's last post.

(But I've tried stranger things <vbg.)

Tom Ogilvy wrote:

I don't believe I suggested application level events (or see any reason to
have them), so I don't know where he is going with that.

--
Regards,
Tom Ogilvy

"Dave Peterson" wrote in message
...
The Workbook_Open routine should be behind the ThisWorkbook module.

(or rename it to Auto_open and keep it in a General module.)

I haven't followed the thread, but I don't think you need an application
event
for your purposes.

Tom Doster wrote:

Thanks Tom O.,
I got the example toolbar code for Sub NewToolBar() working, but only
when I
run it manually. I'm having in trouble in general getting anything to
happen
on workbook open.

I've tried the following function in a module - nothing.
Private Sub Workbook_Open()
MsgBox "Workbook is Now Open!"
End Sub

And I've tried events. I've got one class module called EventClassModule
containing:
Public WithEvents App As Application
Private Sub App_SheetActivate(ByVal Sh As Object)
MsgBox "Sheet Activated"
End Sub
Private Sub App_WorkbookOpen(ByVal Wb As Workbook)
MsgBox "Workbook Opened"
End Sub

And I've got a module containing:
Dim X As New EventClassModule
Sub InitializeApp()
Set X.App = Application
End Sub

After running the InitializeApp() manually, App_SheetActivate() works!
That's good. If I save the .xls and open it however App_WorkbookOpen()
doesn't seem to run. I would think that somehow I have to run
InitializeApp() first thing when the workbook opens. But if I could do
that
then why would I care about any of this event stuff? Why wouldn't I just
call the NewToolbar() subroutine and be done with it.

I read the through the documents you suggested but must be missing
something. Can you help?

Tom
"Tom Doster" wrote in 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







--

Dave Peterson

--

Dave Peterson

Thanks All.

Tom O.: Not knowing much of anything about 'events' I guess I read "It
would be better to build the toolbar with code in the open event of the
addin." as 'application-level events'. I moved 'Workbook_Open()' to the
ThisWorkbook module and ta da! -- everything works well. :-)

At least now I know how to work with application-level events should I ever
need them! ;-)

Thanks Again!
Tom Doster

"Tom Ogilvy" wrote in message
...
I don't believe I suggested application level events (or see any reason to
have them), so I don't know where he is going with that.

--
Regards,
Tom Ogilvy

"Dave Peterson" wrote in message
...
The Workbook_Open routine should be behind the ThisWorkbook module.

(or rename it to Auto_open and keep it in a General module.)

I haven't followed the thread, but I don't think you need an application
event
for your purposes.

Tom Doster wrote:

Thanks Tom O.,
I got the example toolbar code for Sub NewToolBar() working, but only
when I
run it manually. I'm having in trouble in general getting anything to
happen
on workbook open.

I've tried the following function in a module - nothing.
Private Sub Workbook_Open()
MsgBox "Workbook is Now Open!"
End Sub

And I've tried events. I've got one class module called
EventClassModule
containing:
Public WithEvents App As Application
Private Sub App_SheetActivate(ByVal Sh As Object)
MsgBox "Sheet Activated"
End Sub
Private Sub App_WorkbookOpen(ByVal Wb As Workbook)
MsgBox "Workbook Opened"
End Sub

And I've got a module containing:
Dim X As New EventClassModule
Sub InitializeApp()
Set X.App = Application
End Sub

After running the InitializeApp() manually, App_SheetActivate() works!
That's good. If I save the .xls and open it however App_WorkbookOpen()
doesn't seem to run. I would think that somehow I have to run
InitializeApp() first thing when the workbook opens. But if I could do
that
then why would I care about any of this event stuff? Why wouldn't I
just
call the NewToolbar() subroutine and be done with it.

I read the through the documents you suggested but must be missing
something. Can you help?

Tom
"Tom Doster" wrote in 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







--

Dave Peterson