Peter, a few comments -

Do you have the MS Forms 2.0 reference I mentioned earlier in this thread.
In VBA, easiest way is to (temporarily) add a Userform. After typing "mbtn
As MSForms." you should start seeing the intellisense after the dot and when
done the prefilled events in the drop down combo (first select mbtn in the
left combo).

If your code is in VBA and you are not automating another instance of Excel,
you don't need the xlApp stuff (though no harm that way).

If Not ActiveCell Is Nothing _
And TypeOf ActiveSheet Is Worksheet Then

Good stuff and looks like you are trying to be careful in case of chart
sheets or a chartobject.chart selected. But if say the activesheet is not a
worksheet the line might error with Activecell. It's a bit inconsistent
though, if you do need to use xlApp (eg automation), both ActiveCell &
ActiveSheet should be qualified with xlApp. Also if the code is not in Excel
use "Excel.Worksheet".

Not sure why your static counter is not incrementing, but probably better to
do something like this

Dim ole As OLEObject
For Each ole In ActiveSheet.OLEObjects
If InStr(ole.progID, "CommandButton") Then
counter = counter + 1
End If
Next
counter = counter + 1

It's normal not to be able to break into code after having added an ActiveX
control, don't try and don't worry about it.

Finally, best not to programmatically add worksheet controls to the same
workbook that contains the code that's adding the controls (risk of crashing
Excel as code recompiles, maybe that's why your static counter loses scope).
It's a good idea to trap the close event of the wb so you can destroy the
class objects.

Regards,
Peter T


wrote in message
...
Peter T,
I'm having some problems with your example code;
With this in a class module called "clsBtnEvents":

Option Explicit
Private WithEvents mbtn As MSForms.CommandButton

Friend Property Set Button(ByRef btn As MSForms.CommandButton)
Set mbtn = btn
End Property
Private Sub mbtn_Click()
MsgBox "Button with caption """ & mbtn.Caption & """ was clicked."
End Sub


An this in a normal code module:

Option Explicit
Dim objBtnEventHandlers() As New clsBtnEvents

Sub InsertButtonInActiveCell()
Static slngBtnNum As Long
Dim ole As Excel.OLEObject
Dim btn As MSForms.CommandButton
Dim appXl As Excel.Application: Set appXl =
ThisWorkbook.Application

If Not ActiveCell Is Nothing _
And TypeOf ActiveSheet Is Worksheet Then
With appXl.ActiveCell
Set ole = appXl.ActiveSheet.OLEObjects.Add( _
"Forms.CommandButton.1", _
Left:=.Left, Top:=.Top, _
Width:=.Width, Height:=.Height)
End With
With ole
slngBtnNum = slngBtnNum + 1
.Object.Caption = "Button " & CStr(slngBtnNum)
ReDim Preserve objBtnEventHandlers(1 To slngBtnNum)
Set objBtnEventHandlers(slngBtnNum).Button = .Object
End With
End If
End Sub

I can't seem to trap the click event, also the static variable
slngBtnNum doesn't increase resulting all buttons having the name
"Button 1".. I've tried to figure out whats the problem, but can't
seem to find it.. Do you see any obvious mistakes?

I've also found that I cannot set breakpoints below the line that adds
the button to the sheet, I get the message "Can't enter break mode at
this time".

best regards
Peder Schmedling

On Jan 7, 11:51 am, "Peter T" <peter_t@discussions wrote:
Dim ole as Excel.OLEObject
With xlApp.ActiveCell
Set ole = xlApp.ActiveSheet.OLEObjects.Add("Forms.CommandBut ton.1", _
Left:=.Left, Top:=.Top, Width:=115#, Height:=24#)
End With
ole.Visible = True

' trap the button's events in a WithEvents class
Set c = New clsBtnEvents
Set c.btn = ole.Object

Regards,
Peter T