Solved: Add In Problems

[samuelwright]

Hey all, hope you are well

Im having a slight problem with an add-in that I have written in Excell

I have written (with help from loads of VBAX users!) a piece of VBA (please see below) in a spreadsheet module that I subsequently saved as an Add-In to allow that code to be available everytime I open a new Excell Spreadsheet. However, it doesnt want to play!! That is, the events (i.e. Workbook_Open) that I think should be happening are not!! In fact, when i try to search for any macros in a new spreadsheet, I cannot find any, despite the fact that the Add-In has them!Can anyone see if what I have written is wrong?

[VBA]
Option Explicit

Sub Workbook_Open()
DeleteMenu 'delete any existing menus
BuildMenu 'create new menu
End Sub

Sub Workbook_BeforeClose(Cancel As Boolean)
DeleteMenu
End Sub

Private Sub BuildMenu()

Dim cb As CommandBarControl

With Application.CommandBars("Worksheet Menu Bar")
Set cb = .Controls.Add(msoControlPopup, Temporary:=True)
With cb
.Caption = "UserForm Open"
.OnAction = "FOI"
End With
End With

End Sub

Private Sub DeleteMenu()

Dim cb As CommandBarControl
For Each cb In Application.CommandBars("Worksheet Menu Bar").Controls
If cb.Caption = "UserForm Open" Then cb.Delete
Next
End Sub

Private Sub FOI()
If Application.Workbooks.Count > 0 Then
Load SubjectNameForm
SubjectNameForm.Show
Else
MsgBox "Nothing to save"
End If

End Sub
[/VBA]

So there you go!! Am I being a clutz?

[tpoynton]

I'm no expert here, but perhaps a little more info will help...have you installed the addin through the addins dialog box?

the code will not appear in a new spreadsheet, but if the addin is installed, you may need to use different events such as Workbook_AddinInstall() instead of workbook open. also, I believe the code will need to reside in the "this workbook" sheet for the addin to work properly...

again, i'm no expert...also, there are two steps (i believe) to creating the addin - first, save the file as type xla, then set the property in thisworkbook "issaddin" to true.

hope this helps!

[Killian]

I don't think the problem is with what's written - that all works fine.
Can we just clarify where the code is, in terms of the project structure? - you mention a "spreadsheet module", the WorkBook_Open and Workbook_BeforeClose events should be in the WorkBook code module... the Build and Delete toolbar routines can also go as private Subs in the workbook module.
The other routines you call from menu buttons should go in a standard module(s) (Insert>Module in th VBE). They will need to be Public so they can be called from other modules.

[Bob Phillips]

The other routines you call from menu buttons should go in a standard module(s) (Insert>Module in th VBE). They will need to be Public so they can be called from other modules.

They can be Private in a standard code module, don't have to be public unless invoked from other than the menu in other modules.

[samuelwright]

Oops! I had saved the Workbook open and before close events in a standard module, not the ThisWorkbook. I have transferred it, saved it as a Add-In and voila it works.

Geniuses! Thank you!!