I just created an excel add-in . I want my users to be able to add it to
their workbook or it should always be there whenever excel workbook is
opened. €¦ Something like the personal workbook.

Also, how do I make it available for the macro to run without the user going
through the VB editor to run the macro? Something like going through the
tools-macro-choose macro name €“run or maybe a custom button on the menu or
toolbar

thanks

Save the add-in to a shared location - users can go to tools/add-ins and
browse to your add-in, copying a copy to their C drive when prompted.


To create a menu, use something like the following in the workbook open event:

On Error Resume Next
Application.CommandBars("Worksheet Menu Bar").Controls("My Menu").Delete
On Error GoTo 0

Dim wsmb As CommandBar
Dim myMen As CommandBarControl

Set wsmb = Application.CommandBars("Worksheet Menu Bar")
Set myMen = wsmb.Controls.Add(Type:=msoControlPopup, temporary:=True)

With myMen
.Caption = "My Menu"
With .Controls.Add(msoControlButton)
.Caption = "First menu option"
.OnAction = "{macro name}"
End With
With .Controls.Add(msoControlButton)
.Caption = "Second menu option"
.OnAction = "{macro name}"
End With
end with

"mju" wrote:

I just created an excel add-in . I want my users to be able to add it to
their workbook or it should always be there whenever excel workbook is
opened. €¦ Something like the personal workbook.

Also, how do I make it available for the macro to run without the user going
through the VB editor to run the macro? Something like going through the
tools-macro-choose macro name €“run or maybe a custom button on the menu or
toolbar

thanks

Thanks alot Sam!

It works perfectly fine when i open an excel file (xls) but the macro does
not run when i open a csv file in excel. it tells me the macro cannot be
found.


"Sam Wilson" wrote:

Save the add-in to a shared location - users can go to tools/add-ins and
browse to your add-in, copying a copy to their C drive when prompted.


To create a menu, use something like the following in the workbook open event:

On Error Resume Next
Application.CommandBars("Worksheet Menu Bar").Controls("My Menu").Delete
On Error GoTo 0

Dim wsmb As CommandBar
Dim myMen As CommandBarControl

Set wsmb = Application.CommandBars("Worksheet Menu Bar")
Set myMen = wsmb.Controls.Add(Type:=msoControlPopup, temporary:=True)

With myMen
.Caption = "My Menu"
With .Controls.Add(msoControlButton)
.Caption = "First menu option"
.OnAction = "{macro name}"
End With
With .Controls.Add(msoControlButton)
.Caption = "Second menu option"
.OnAction = "{macro name}"
End With
end with

"mju" wrote:

I just created an excel add-in . I want my users to be able to add it to
their workbook or it should always be there whenever excel workbook is
opened. €¦ Something like the personal workbook.

Also, how do I make it available for the macro to run without the user going
through the VB editor to run the macro? Something like going through the
tools-macro-choose macro name €“run or maybe a custom button on the menu or
toolbar

thanks

Thanks alot Sam!

It works perfectly fine when i open an excel file (xls) but the macro does
not run when i open a csv file in excel. it tells me the macro cannot be
found.


"Sam Wilson" wrote:

Save the add-in to a shared location - users can go to tools/add-ins and
browse to your add-in, copying a copy to their C drive when prompted.


To create a menu, use something like the following in the workbook open event:

On Error Resume Next
Application.CommandBars("Worksheet Menu Bar").Controls("My Menu").Delete
On Error GoTo 0

Dim wsmb As CommandBar
Dim myMen As CommandBarControl

Set wsmb = Application.CommandBars("Worksheet Menu Bar")
Set myMen = wsmb.Controls.Add(Type:=msoControlPopup, temporary:=True)

With myMen
.Caption = "My Menu"
With .Controls.Add(msoControlButton)
.Caption = "First menu option"
.OnAction = "{macro name}"
End With
With .Controls.Add(msoControlButton)
.Caption = "Second menu option"
.OnAction = "{macro name}"
End With
end with

"mju" wrote:

I just created an excel add-in . I want my users to be able to add it to
their workbook or it should always be there whenever excel workbook is
opened. €¦ Something like the personal workbook.

Also, how do I make it available for the macro to run without the user going
through the VB editor to run the macro? Something like going through the
tools-macro-choose macro name €“run or maybe a custom button on the menu or
toolbar

thanks

.OnAction = "{macro name}"

This should be

..OnAction = "'" & ThisWorkbook.name & "'!MacroName"

This tells Excel where to look for the procedure.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)





On Fri, 6 Nov 2009 10:02:12 -0800, mju
wrote:

Thanks alot Sam!

It works perfectly fine when i open an excel file (xls) but the macro does
not run when i open a csv file in excel. it tells me the macro cannot be
found.


"Sam Wilson" wrote:

Save the add-in to a shared location - users can go to tools/add-ins and
browse to your add-in, copying a copy to their C drive when prompted.


To create a menu, use something like the following in the workbook open event:

On Error Resume Next
Application.CommandBars("Worksheet Menu Bar").Controls("My Menu").Delete
On Error GoTo 0

Dim wsmb As CommandBar
Dim myMen As CommandBarControl

Set wsmb = Application.CommandBars("Worksheet Menu Bar")
Set myMen = wsmb.Controls.Add(Type:=msoControlPopup, temporary:=True)

With myMen
.Caption = "My Menu"
With .Controls.Add(msoControlButton)
.Caption = "First menu option"
.OnAction = "{macro name}"
End With
With .Controls.Add(msoControlButton)
.Caption = "Second menu option"
.OnAction = "{macro name}"
End With
end with

"mju" wrote:

I just created an excel add-in . I want my users to be able to add it to
their workbook or it should always be there whenever excel workbook is
opened. … Something like the personal workbook.

Also, how do I make it available for the macro to run without the user going
through the VB editor to run the macro? Something like going through the
tools-macro-choose macro name –run or maybe a custom button on the menu or
toolbar

thanks

thanks Chip!

When you siad workbook name, do you mean the name of the work? Please see below on action code. I am still getting the error.

Private Sub Workbook_Open()
On Error Resume Next
Application.CommandBars("Worksheet Menu Bar").Controls("PO_DATA ANALYSIS MACRO").Delete
On Error GoTo 0

Dim wsmb As CommandBar
Dim myMen As CommandBarControl

Set wsmb = Application.CommandBars("Worksheet Menu Bar")
Set myMen = wsmb.Controls.Add(Type:=msoControlPopup, temporary:=True)

With myMen
.Caption = "PO_DATA ANALYSIS MACRO"
With .Controls.Add(msoControlButton)
.Caption = "PLAY"
.OnAction = "'" & PO_data_Current - Week.csv & "'! PO_Summary_Rpt "
' .OnAction = "PO_Summary_Rpt"
End With
' End With
End With




Chip Pearson wrote:

This should be.OnAction = "'" & ThisWorkbook.name & "'!
06-Nov-09

This should be

..OnAction = "'" & ThisWorkbook.name & "'!MacroName"

This tells Excel where to look for the procedure.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)





wrote:

Previous Posts In This Thread:

EggHeadCafe - Software Developer Portal of Choice
ASP.NET SqlCacheDependency Redux
http://www.eggheadcafe.com/tutorials...dependenc.aspx

CSV files are plain text (unless you're doing something very strange).

They don't contain macros (or formulas or formatting or all that stuff that
makes excel files useful and pretty.

mju wrote:

Thanks alot Sam!

It works perfectly fine when i open an excel file (xls) but the macro does
not run when i open a csv file in excel. it tells me the macro cannot be
found.

"Sam Wilson" wrote:

Save the add-in to a shared location - users can go to tools/add-ins and
browse to your add-in, copying a copy to their C drive when prompted.


To create a menu, use something like the following in the workbook open event:

On Error Resume Next
Application.CommandBars("Worksheet Menu Bar").Controls("My Menu").Delete
On Error GoTo 0

Dim wsmb As CommandBar
Dim myMen As CommandBarControl

Set wsmb = Application.CommandBars("Worksheet Menu Bar")
Set myMen = wsmb.Controls.Add(Type:=msoControlPopup, temporary:=True)

With myMen
.Caption = "My Menu"
With .Controls.Add(msoControlButton)
.Caption = "First menu option"
.OnAction = "{macro name}"
End With
With .Controls.Add(msoControlButton)
.Caption = "Second menu option"
.OnAction = "{macro name}"
End With
end with

"mju" wrote:

I just created an excel add-in . I want my users to be able to add it to
their workbook or it should always be there whenever excel workbook is
opened. €¦ Something like the personal workbook.

Also, how do I make it available for the macro to run without the user going
through the VB editor to run the macro? Something like going through the
tools-macro-choose macro name €“run or maybe a custom button on the menu or
toolbar

thanks


--

Dave Peterson

i just tried this but still same error

..OnAction = "'" & ThisWorkbook.Name & "'!PO_Summary_Rpt"



maju maju wrote:

add -in
06-Nov-09

thanks Chip!

When you siad workbook name, do you mean the name of the work? Please see below on action code. I am still getting the error.

Private Sub Workbook_Open()
On Error Resume Next
Application.CommandBars("Worksheet Menu Bar").Controls("PO_DATA ANALYSIS MACRO").Delete
On Error GoTo 0

Dim wsmb As CommandBar
Dim myMen As CommandBarControl

Set wsmb = Application.CommandBars("Worksheet Menu Bar")
Set myMen = wsmb.Controls.Add(Type:=msoControlPopup, temporary:=True)

With myMen
.Caption = "PO_DATA ANALYSIS MACRO"
With .Controls.Add(msoControlButton)
.Caption = "PLAY"
.OnAction = "'" & PO_data_Current - Week.csv & "'! PO_Summary_Rpt "
' .OnAction = "PO_Summary_Rpt"
End With
' End With
End With

Previous Posts In This Thread:

EggHeadCafe - Software Developer Portal of Choice
ASP.NET DropDown ListBox and XML Databinding
http://www.eggheadcafe.com/tutorials...n-listbox.aspx