MrExcel Publishing
Your One Stop for Excel Tips & Solutions

How to count buttons in a sheet ?

Posted by moujia on May 19, 2000 3:53 AM

Hi! everbody

My problem is: I inserted in one sheet n buttons,
each button refering to one sheet. And that sheet
has more buttons like "create new sheet" etc.
How could I count how many buttons has a sheet
to determine the number to assign to a new button.

Thanks in advance

Posted by Ivan Moala on May 19, 2000 8:03 PM

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

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



Posted by moujia on May 20, 2000 6:26 PM

Thank you

and best regards.