Hi Robin,
I gave this another try and simply cannot get the ProgID set up against a
forms button, whether done through VBA or manually in Excel. I either get a
"Macro cannot be found" or "the formula you typed contains an error
message". I am trying to set OnAction to something like this:
!<ComAddInName.clsDesignerEventHandler>
which matches exactly what I have for the commandbarbuttons. The com add in
exists and is loaded with a forms.commandbutton event handler set up and
waiting.
Any suggestions?
OK, when I said "it works just like a commandbarbutton", I guess I wasn't
being too precise <g>.
What I meant was that responding to the controls' events works just like a
commandbarbutton, in that we have to create a class module with a variable
declared WithEvents, then hook an instance of that class to each
commandbarbutton we want to respond to. We respond to controls on sheets the
same way: We have to have a class module with a WithEvents declaration:
Class CBtnEvents:
Public WithEvents mcmdButton As MSForms.CommandButton
Private Sub mcmdButton_Click()
MsgBox "You clicked me!"
End Sub
The some code to hook up an instance of the class to each button on a sheet
that we want to respond to. Typically (I guess), this would happen in response
to an Application-level WorkbookOpen event, where we check if the workbook is
one of 'ours' (e.g. by looking at a document property) and setting up the
hook. So in the Connect class, we might have (untested and watch out for
word-wrap):
Dim WithEvents mxlApp As Excel.Application
Dim mcolEvents As Collection
Private Sub AddinInstance_OnConnection(ByVal Application As Object, ByVal
ConnectMode As AddInDesignerObjects.ext_ConnectMode, ByVal AddInInst As
Object, custom() As Variant)
'Set up the application event hooks
Set mxlApp = Application
End Sub
'Respond to a workbook being opened
Private Sub mxlApp_WorkbookOpen(ByVal Wb As Workbook)
Dim clsBtnEvents As CBtnEvents
'Is it one of ours?
If IsOurWorkbook(Wb) Then
'Yes, so initialise the event-holding collection if not already done
If mcolEvents Is Nothing Then Set mcolEvents = New Collection
'Hook whichever buttons we need to
Set clsBtnEvents = New CBtnEvents
'Untested. Might need .Object.Object
Set clsBtnEvents.mcmdButton =
Wb.Worksheets(1).Shapes("btnStart").Object
mcolEvents.Add clsBtnEvents
End If
End Sub
'Determine if it's our workbook, by checking a custom document property
Function IsOurWorkbook(ByRef Wb As Workbook) As Boolean
On Error Resume Next
IsOurWorkbook = (Wb.CustomDocumentProperties("WorkbookType") =
"MyCustomWorkbook")
End Function
None of this has anything to do with whether the OnAction is set to anything.
The only time setting the OnAction comes into play with commandbarbuttons is
to have demand-loaded COM Addins, whereby the addin is initially set to 'Load
at next startup only'. When first started, it creates its menu items and
doesn't remove them. When Excel next starts, the menu items will be there, but
the addin won't be loaded. It is only loaded when the button is clicked; the
OnAction setting tells Excel with Addin to load in that case.
Controls on worksheets don't support the use of OnAction in the same way -
hence we have to make sure our addins are set to 'Load at startup' and have
code to look for workbooks being opened or created that we might want to
respond to (as shown above).
Hope that helps.
Regards
Stephen Bullen
Microsoft MVP - Excel
www.oaltd.co.uk