Hi Peter,
thanks for your quick reply.

To summarize:
Yes I have the MS Forms reference, I'm heavily addicted to
intellisens :-P
I knew about the appXl/automation stuff, but thanks for pointing it
out.
Great tip about ActiveCell/ActiveSheet, I was trying to be careful but
I can now see that the code has flaws.

The final note you made, about not adding controls to the workbook
containing the "adding code" did the trick. When I modified the code
to create a new workbook it worked flawlessly.

As a side not I can tell you that I did not end up counting the number
of buttons in the sheet, I was worried that this would cause problems
with the RedDim statement.. Instead I implemented a check of bounds to
the event handler array.

Again thanks for your help,
Peder Schmedling

On Jan 9, 10:30*am, "Peter T" <peter_t@discussions wrote:
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