Creating Excel add-in

[screamager]

Hi,

I'm trying to create an add-in for Excel 2000 with VBA, but can't seem to get past even the first obstacle.

I add this to the workbook code:

Private Sub Workbook_AddinInstall()
Dim asdf As CommandBar
Dim qwer As CommandBarButton

Set asdf = Application.CommandBars("File&quot
Set qwer = asdf.Controls.Add(Type:=msoControlButton)
qwer.Caption = "TEST"
qwer.OnAction = "DoItNow"
End Sub

Sub DoItNow()
MsgBox "YEAH!"
End Sub

Private Sub Workbook_AddinUninstall()
Dim asdf As CommandBar
Dim qwer As CommandBarButton

Set asdf = Application.CommandBars("File&quot

For x = asdf.Controls.Count To 1 Step -1
If asdf.Controls(x).Caption = "TEST" Then
asdf.Controls(x).Delete
End If
Next
End Sub

..and then I save the work book as an add-in (.xla file). All I get when i press the 'TEST' menu item is an error telling me 'The Macro test.xla!DoItNow cannot be found' or something to that effect. Am i missing something really obvious or what?

 

[TonyJollans]

Hi screamager,

I'm no expert with AddIns but just coding the above and saving as an AddIn won't really have any effect. Even to get the menu item added you must have run some of the code. When you have an AddIn I think it needs to be open when you try to use it and ought to be somewhere where it is automatically opened like C:\Program Files\Microsoft Office\Office\XLStart

Enjoy,
Tony

 

[screamager]

The code for adding and removing the menu (ie, the AddinInstall and AddinUninstall events) is fired when you select the addin under the tools menu. If you select the addin it will be loaded the next time as well. To actually get it working as an addin is not the problem though, i can't get it to fire the sub from the menu item.

 

[combo]

Hi,
Your add-in has to be installed. Check it in the Tools>Add-ins window.
It can be safer to use open/close events to create/delete toolbar. Those events take place when you excel opens/closes with installed add-in or you install/uninstall it.

combo

 

[screamager]

The add-in IS installed, otherwise I wouldn't even have the menu item to click...

 

[TonyJollans]

Hi,

Reading between the lines a bit, is your code in "ThisWorkbook"? Try putting it in a code module. Or, change the OnAction to "Thisworkbook.DoItNow".

Enjoy,
Tony

 

[combo]

Try uninstall your add-in and check if the menu was cleared. Install it again. Now all should work. if not - check if sub name is the same as in OnAction.

combo

 

[screamager]

I've already tried using thisworkbook.doitnow, i've tried application.thisworkbook.doitnow and myproject.myworkbook.doitnow as well.

Combo, As I said earlier. The add-in itself works fine (that is installing and uninstalling it), the code in the Install and Uninstall events do what they're supposed to do. It's getting the OnAction event to work that is the problem

 

[screamager]

...oh, and the code is as seen above. The actual code that i want to run is quite different from a msgbox but I wrote this simple code to see if i could reproduce the problem

 

[TonyJollans]

Sorry, then, I don't know what to suggest - it works for me as thisworkbook.doitnow and just as doitnow if its in a code module.

 

[screamager]

Tony, you genius you! I didn't notice the part about the code module earlier. For some reason that worked like a charm for me... thanks