Custom button & VBA function

[Nathan]

Hope someone can help, I've created a template that adds temporary custom
buttons to both the file menu and standard toolbar calling a VBA function.

I also ensure that if the user opens many worksheets based on the template
the custom buttons are only loaded once.

But here in lies my problem, if the user does create more than one worksheet
from the template then when they click on the button the VBA function is only
ever called on the first worksheet they opened.

I've included the code below, my guess is that I've got the OnAction
property set incorrectly.

Private Sub Workbook_Open()

Dim ctl As CommandBarControl
Dim intMenuPosition As Integer
Dim intToolBarPosition As Integer

On Error GoTo Workbook_Open_Fail

'Check to see if our buttons are loaded already, if so don't attempt
'to load them again
Set ctl = Application.CommandBars("File").FindControl(Tag:="CORGISave")
If ctl Is Nothing Then

'Find the control positions that we want to base the location of our
new controls on
'Find the Save As menu item
Set ctl = Application.CommandBars("File").FindControl(ID:=748)
intMenuPosition = ctl.Index

Set ctl = Application.CommandBars("Standard").FindControl(ID:=3)
intToolBarPosition = ctl.Index

'increment the positions as we want our items after the item we
found and the function
'wants to know what item we want to put our item before
intMenuPosition = intMenuPosition + 1
intToolBarPosition = intToolBarPosition + 1

'Add 'Save Notification Data' item to file menu and the standard
toolbar
CustomButton
Application.CommandBars("File").Controls.Add(msoControlButton, , ,
intMenuPosition, True), CORGICustomButton.CORGISave
CustomButton
Application.CommandBars("Standard").Controls.Add(msoControlButton, , ,
intToolBarPosition, True), CORGICustomButton.CORGISave

'Add 'Open Notification Data' item to file menu and the standard
toolbar
CustomButton
Application.CommandBars("File").Controls.Add(msoControlButton, , ,
intMenuPosition + 1, True), CORGICustomButton.CORGIOpen
CustomButton
Application.CommandBars("Standard").Controls.Add(msoControlButton, , ,
intToolBarPosition + 1, True), CORGICustomButton.CORGIOpen



End If

Exit Sub

Workbook_Open_Fail:
MsgBox "An error occured configuring excel for .", vbCritical, CUSTOMTITLE
End Sub


Private Sub CustomButton(objControl As CommandBarControl, enuButton As
CORGICustomButton)


'*******************************************************************************
'
' Procedure formats a commandbarcontrol that is passed in based upon
what entry it represents
' as identified in the CORGICustomButton enumeration
'

'*******************************************************************************

Dim strCaption As String
Dim strAction As String
Dim intFaceID As Integer
Dim strTag As String

'Base on enum set values to use for control
Select Case enuButton

Case CORGICustomButton.CORGISave
strCaption = "Save Notification data"
strAction = "thisworkbook.SaveNotificationDataFile"
intFaceID = 271
strTag = "CORGISave"

Case CORGICustomButton.CORGIOpen
strCaption = "Open Notification data"
strAction = "thisworkbook.OpenNotificationDatafile"
intFaceID = 270
strTag = "CORGIOpen"

End Select


'Set properties of the control passed in
With objControl
.Caption = strCaption
.Style = msoButtonIconAndCaption
.FaceId = intFaceID
.OnAction = strAction
.Tag = strTag
End With

End Sub