I've created some VB code and turned it / saved it as an Add-In. Now I want
to be able to run the code from other worksheets. How to do it? Ideally,
when the add-in is loaded, it would create a command button or menu item
which the user then selects to run the add-in.

Note I am not an advanced coder / user....

Suggestions welcome!
--
Byron M

Why not create a menu item on a toolbar and the user could click on that.


http://support.microsoft.com/default...02&Product=xlw
How to customize menus and menu bars in Excel

http://msdn2.microsoft.com/en-us/lib...ffice.11).aspx

--
Regards,
Tom Ogilvy



"Byron M" wrote:

I've created some VB code and turned it / saved it as an Add-In. Now I want
to be able to run the code from other worksheets. How to do it? Ideally,
when the add-in is loaded, it would create a command button or menu item
which the user then selects to run the add-in.

Note I am not an advanced coder / user....

Suggestions welcome!
--
Byron M

An add-in is really nothing more than a collection of subroutines
(i.e. macros) and/or functions. You don't really "run" it.
Typically, you would make it active via the Tools Add-Ins menu
option. From that point, you can run any of the macros or use any of
the functions within the add-in.

As far as the add-in creating command buttons or menu items, you'd
need to have something in the workbook being opened that would utilize
those macros within the add-in to do the creation. Say, within the
workbook open event. Or you could manually create a button on your
worksheet that uses a macro *from* the add-in.

I suppose you could put the add-in in the XLSTART directory to have it
automatically generate menus and the like, but I'm not sure I'd want
it to do that.

Byron M wrote:
I've created some VB code and turned it / saved it as an Add-In. Now I want
to be able to run the code from other worksheets. How to do it? Ideally,
when the add-in is loaded, it would create a command button or menu item
which the user then selects to run the add-in.

Note I am not an advanced coder / user....

Suggestions welcome!

What I'm trying to do is to cause a user input form I created to appear, and
then the data on the spreadsheet is processed according to the variables
input by the user. I guess where I'm having a problem is to make the form
appear ("run"). I'm able to do this by going to the VB editor, viewing the
form, and hitting run, but I'm hoping to make this a little easier for the
user....
--
Byron M


"Randy Harmelink" wrote:

An add-in is really nothing more than a collection of subroutines
(i.e. macros) and/or functions. You don't really "run" it.
Typically, you would make it active via the Tools Add-Ins menu
option. From that point, you can run any of the macros or use any of
the functions within the add-in.

As far as the add-in creating command buttons or menu items, you'd
need to have something in the workbook being opened that would utilize
those macros within the add-in to do the creation. Say, within the
workbook open event. Or you could manually create a button on your
worksheet that uses a macro *from* the add-in.

I suppose you could put the add-in in the XLSTART directory to have it
automatically generate menus and the like, but I'm not sure I'd want
it to do that.

Byron M wrote:
I've created some VB code and turned it / saved it as an Add-In. Now I want
to be able to run the code from other worksheets. How to do it? Ideally,
when the add-in is loaded, it would create a command button or menu item
which the user then selects to run the add-in.

Note I am not an advanced coder / user....

Suggestions welcome!

The key phrase there is "cause...to appear". The question is WHAT
should trigger it? The routine can be in your add-in, but you'll
still need something in the workbook to trigger the event that causes
it to run. It can be as simple as putting into the "workbook open"
event, or you could attach it to a button, or any number of other ways
-- but they will depend on the nature of the routine.

On Jun 10, 2:12 pm, Byron M wrote:
What I'm trying to do is to cause a user input form I created to appear, and
then the data on the spreadsheet is processed according to the variables
input by the user. I guess where I'm having a problem is to make the form
appear ("run"). I'm able to do this by going to the VB editor, viewing the
form, and hitting run, but I'm hoping to make this a little easier for the
user....

Here's some code I wrote based on the links Tom provided. I've got exactly
the same issue as you. You should be able to copy this into your .xla
module. The user would have to manually run the "AddMenuButton" macro using
the Tools...Macro menu item. (The macro won't show up in the list, but the
macro can still be run.) You could probably trigger an automatic load using
the tips provided by Randy.

=======================

Sub AddMenuButton()

'Creates a command button on the Standard toolbar

Dim mCaption As String
Dim objCommandBar As Office.CommandBar
Dim objCommandBarControl As Office.CommandBarControl
Dim objCommandBarButton As Office.CommandBarButton

mCaption = "Button Text"

Set objCommandBar = Application.CommandBars("Standard")

For Each objCommandBarControl In
Application.CommandBars("Standard").Controls
If objCommandBarControl.Caption = mCaption Then
objCommandBarControl.Delete
Next objCommandBarControl

With objCommandBar.Controls

Set objCommandBarButton = .Add(msoControlButton)

With objCommandBarButton

.Caption = mCaption
.Style = msoButtonCaption
.TooltipText = "Bring up the CPS PV Export control form"
.OnAction = "DisplayForm"
End With

End With

End Sub