Hi Moujia
You didn't specify which version of excel you use?
Assumming xl97+ then you have to understand that
there are 2 types of Controls.
Activex and Excel form controls the latter been
less flexable in what you can do with it via it's
properties and methods.
The following macro will give you these counts.
Sub GetButtonCounts()
'===================================================================
'= Procedure: GetButtonCounts =
'= Type: Subprocedure =
'= =
'= Purpose: Get count of Form commandbuttons, count of ActiveX =
'= commandbuttons & ALL embeded ActiveX controls. =
'= Done originally for Moujia =
'= Parameters: None =
'= Returns: Nothing =
'= =
'= Version: Date: Developer: Action: =
'=---------|---------|---------------|-----------------------------=
'= 1.0.0 |20-May-00| Ivan F Moala | Created =
'===================================================================
Dim BtnFm As Integer
Dim BtnActX As Integer
Dim O_OLEobjs As Integer
Dim MyShapes As OLEObjects
Dim Btn As OLEObject
'OLE Programmatic Identifiers for Commandbuttons = Forms.CommandButton.1
Set MyShapes = ActiveSheet.OLEObjects
For Each Btn In MyShapes
If Btn.ProgId = "Forms.CommandButton.1" Then
BtnActX = BtnActX + 1
End If
Next
BtnFm = ActiveSheet.Buttons.Count
O_OLEobjs = MyShapes.Count
MsgBox "Number of FormButtons= " & BtnFm & Chr(13) & _
"Number of ActiveX CommandButtons= " & BtnActX & Chr(13) & _
"Number of ActiveX Controls=" & O_OLEobjs
End Sub
Regards
Ivan