samuelwright
11-24-2005, 07:05 AM
Hey all, hope you are well :hi:
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?
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
So there you go!! Am I being a clutz? :mkay
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?
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
So there you go!! Am I being a clutz? :mkay