Running/accessing Add-in

[BostjanPerdan]

Hi all!

I have created an add-in to share with co-workers. I would like to know, if it is possible to automatically create a new toolbar and a suitable button when the add-in is loaded on the computer? How is this done? How do I run / access the add-in in the first place (I am not sure, I am doing it right.)?

Cheers,
Bostjan

 

[rmikesmith]

Bostjan,

Second Q. first: Load your add-in by selecting Tools|Add-Ins... from the Excel menu. Click the Browse button and navigate to where the add-in is stored then click OK.

First Q: Yes. Call your procedure to create the toolbar in the Workbook_Open event procedure (ThisWorkbook class module) and your procedure to delete the toolbar in the Workbook_BeforeClose procedure.


Regards,
Mike

 

[firefytr]

Hi Bostjan,

Are you creating your toolbars programmatically? This is definitely the way to go. If you have code you need to sort, post it here. Or if you'd like an example let us know.

Also, are you wanting to share the same addin with your co-workers over a network? Are you distributing it?

-----------
Regards,
Zack Barresse

 

[BostjanPerdan]

Hi all,

First let me explain, what I am trying to do ... I have created a UserForm which I use to load data into a spreadsheet, create charts by selecting names, that are assigned to columns, and move the selected chart (by calculating values in the table) in x or y direction.

I have manually created a custom toolbar and a button which I have used to call the macro while writing the code and debugging it. I have later created an add-in, and I use the same button to run it. However I am not sure that the add-in is actually being run when I click the button?! This is because the original *.xls file opens whenever I click the button. Is this normal for an add-in? How do I assign an add-in to a button?

I would expect the add-in to run unnoticeably in the background? Therefore I would also like the toolbar to be created automatically when the add-in is loaded into Excel (for the first time) and remain there until the user elects to hide or delete it. Are my expectations too high?

Cheers,
Bostjan

 

[firefytr]

No, your expectations are not too high, but you are not donig it right either. You are in fact running your routine from your xls file and not your addin. I'll post some code you can use for creating a toolbar and placing a custom button on it which is linked to a routine in your addin.

This would go in a standard module ...


Option Explicit

Const BAR_NAME As String = "My Bar"

Sub CreateToolbar()
Dim WMB As CommandBar, TB As CommandBar, TBButton As CommandBarButton
DeleteToolbar
Set TB = Application.CommandBars.Add(Name:=BAR_NAME, Position:=msoBarTop, temporary:=False)
TB.Visible = True
With TB.Controls.Add(Type:=msoControlButton) ', ID:=3, Before:=1
.Caption = "Test"
.DescriptionText = "Test"
.FaceId = 426
.OnAction = "Module1.MyTestMacro"
.Style = msoButtonIconAndCaption
.TooltipText = "Test"
End With
End Sub

Sub DeleteToolbar()
On Error Resume Next
Application.CommandBars(BAR_NAME).Delete
End Sub

Sub MyTestMacro()
MsgBox "It's alive!"
End Sub


This would go in your ThisWorkbook module ...


Option Explicit

Private Sub Workbook_AddinInstall()
CreateToolbar
End Sub

Private Sub Workbook_AddinUninstall()
DeleteToolbar
End Sub

Private Sub Workbook_Open()
CreateToolbar
End Sub


Let us know if you need any help with that.

HTH

-----------
Regards,
Zack Barresse