Calling Com addin from form button on sheet?
 

Happy New Year all,

I've got 90% of a transition to a com add-in working after a few hours with
Stephen Bullen's book, but one routine I use places buttons on a worksheet
that I would normally use to call a macro in an xla. I now want those
buttons to call the equivalent routine in the CAI.

I've got command bar and menus working already, so if it is something
similar to that, I'd appreciate a few pointers.

Thanks,

Robin Hammond
www.enhanceddatasystems.com

Calling Com addin from form button on sheet?
 

Hi Robin,

I've got command bar and menus working already, so if it is something
similar to that, I'd appreciate a few pointers.

It's exactly the same, but you need to be using the MSForms controls
(that raise events) rather than the Excel controls (that use OnAction).
You then hook up and handle the buttons' click events exactly the same
as you do for menu items (WithEvents variables etc).

Regards

Stephen Bullen
Microsoft MVP - Excel
www.oaltd.co.uk

Calling Com addin from form button on sheet?
 

Stephen,

Thanks for that. I thought I had tried it and I think the problem was that I
was unable to set the OnAction property for the forms button to the ProgID
at the time that I added it to the sheet, but I will give it another try in
the morning. I'll let you know what happens.

While on this subject. p334 of your book 2002 book. The parameter property
seems flaky when the event fires unless it is explicitly set. I spent a
couple of hours yesterday trying to work out why it worked, then it didn't,
having originally set just the caption. The only way I have got the event
handlers to work reliably is to explicitly set the parameter for the
commandbarbutton to the same value as the caption at the time that the
caption is set in your second code sample.

Robin Hammond
www.enhanceddatasystems.com

"Stephen Bullen" wrote in message
...
Hi Robin,

I've got command bar and menus working already, so if it is something
similar to that, I'd appreciate a few pointers.

It's exactly the same, but you need to be using the MSForms controls
(that raise events) rather than the Excel controls (that use OnAction).
You then hook up and handle the buttons' click events exactly the same
as you do for menu items (WithEvents variables etc).

Regards

Stephen Bullen
Microsoft MVP - Excel
www.oaltd.co.uk

Calling Com addin from form button on sheet?
 

Grüezi Robin Hammond

Robin Hammond schrieb am 05.01.2005

Stephen,

Thanks for that. I thought I had tried it and I think the problem was that I
was unable to set the OnAction property for the forms button to the ProgID
at the time that I added it to the sheet, but I will give it another try in
the morning. I'll let you know what happens.

While on this subject. p334 of your book 2002 book. The parameter property
seems flaky when the event fires unless it is explicitly set. I spent a
couple of hours yesterday trying to work out why it worked, then it didn't,
having originally set just the caption. The only way I have got the event
handlers to work reliably is to explicitly set the parameter for the
commandbarbutton to the same value as the caption at the time that the
caption is set in your second code sample.

Robin Hammond
www.enhanceddatasystems.com

| [Fullquote von 40tude-Dialog[1] entsorgt]


Regards
Thomas Ramel


Fussnote(n):
------------
[1] Programm: http://www.40tude.com/dialog/
Anleitung : http://www.gaehn.org/software/40tude-dialog/tutorial/
--
- MVP for Microsoft-Excel -
[Win XP Pro SP-2 / xl2000 SP-3]

Calling Com addin from form button on sheet?
 

Grüezi together

Sorry, my posting was a mistake - it should be postetd in microsoft.test.


Regards
Thomas Ramel

--
- MVP for Microsoft-Excel -
[Win XP Pro SP-2 / xl2000 SP-3]

Calling Com addin from form button on sheet?
 

Stephen,

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?

Robin Hammond
www.enhanceddatasystems.com

"Robin Hammond" wrote in message
...
Stephen,

Thanks for that. I thought I had tried it and I think the problem was that
I was unable to set the OnAction property for the forms button to the
ProgID at the time that I added it to the sheet, but I will give it
another try in the morning. I'll let you know what happens.

While on this subject. p334 of your book 2002 book. The parameter property
seems flaky when the event fires unless it is explicitly set. I spent a
couple of hours yesterday trying to work out why it worked, then it
didn't, having originally set just the caption. The only way I have got
the event handlers to work reliably is to explicitly set the parameter for
the commandbarbutton to the same value as the caption at the time that the
caption is set in your second code sample.

Robin Hammond
www.enhanceddatasystems.com

"Stephen Bullen" wrote in message
...
Hi Robin,

I've got command bar and menus working already, so if it is something
similar to that, I'd appreciate a few pointers.

It's exactly the same, but you need to be using the MSForms controls
(that raise events) rather than the Excel controls (that use OnAction).
You then hook up and handle the buttons' click events exactly the same
as you do for menu items (WithEvents variables etc).

Regards

Stephen Bullen
Microsoft MVP - Excel
www.oaltd.co.uk

Calling Com addin from form button on sheet?
 

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

Calling Com addin from form button on sheet?
 

Hi Robin,

Set clsBtnEvents.mcmdButton =
Wb.Worksheets(1).Shapes("btnStart").Object

That would need to be:

Wb.Worksheets(1).Shapes("btnStart").OLEFormat.Obje ct.Object

Regards

Stephen Bullen
Microsoft MVP - Excel
www.oaltd.co.uk

Calling Com addin from form button on sheet?
 

Stephen,

Many thanks for such a comprehensive answer. I haven't had time to try it
yet, but now understand what I am aiming for at least.

Robin Hammond
www.enhanceddatasystems.com

"Stephen Bullen" wrote in message
...
Hi Robin,

Set clsBtnEvents.mcmdButton =
Wb.Worksheets(1).Shapes("btnStart").Object

That would need to be:

Wb.Worksheets(1).Shapes("btnStart").OLEFormat.Obje ct.Object

Regards

Stephen Bullen
Microsoft MVP - Excel
www.oaltd.co.uk