kalobit
Messages postés
169
Date d'inscription
mardi 15 juillet 2003
Statut
Membre
Dernière intervention
7 avril 2008
2
23 févr. 2006 à 11:14
salut,
Il faut boucler sur tous les controles de la barre d'outil et tester
La barre de menu d'excel a pour index 1
Ensuite, les menu sont de type msocontrolpopup
ie:
cells(1,1) = "Texte du menu"
Cells(1,2) = "Nom du menu"
for each ctrl in application.commandbars(1).controls
if ctrl.type = msoControlPopup then
compteur = compteur+1
cells(compteur+1, 1) = ctrl.caption
cells(compteur+1,2) = ctrl.name
end if
next
Voilà, pour une liste plus complète, voici ma macro perso :
Sub Lister_les_commandes(CBIndex)
Application.ScreenUpdating = False
Dim BKSortie As Workbook
Application.StatusBar = "Listing en cours"
Set BKSortie = Application.Workbooks.Add(-4167)
ActiveSheet.Name = CommandBars(CBIndex).Name
On Error Resume Next
'Dim menu As CommandBarButton
'Dim CTRL As CommandBarButton
Cells(1, 1) = "Barre de commande"
Cells(1, 2) = ActiveSheet.Name
Cells(2, 1) = "Index de la barre"
Cells(2, 2) = CBIndex
Cells(3, 1) = "Nombre de contrôles"
num = 5
Cells(num, 1) = "MENU"
Cells(num, 2) = "SOUS-MENU"
Cells(num, 3) = "CONTROLE"
Cells(num, 4) = "ID"
Cells(num, 5) = "FaceId"
Cells(num, 6) = "Raccourci Clavier"
For Each menu In Application.CommandBars(CBIndex).Controls
Application.StatusBar = "Listing en cours" & String(num, ".")
Select Case menu.Type
Case msoControlButton
num = num + 1
Cells(num + 1, 3) = menu.Caption
Cells(num + 1, 4) = menu.Id
Cells(num + 1, 5) = menu.FaceId
Cells(num + 1, 6) = menu.ShortcutKey
Case msoControlPopup
num = num + 1
Cells(num + 1, 1) = menu.Caption
Cells(num + 1, 4) = menu.Id
For Each Ctrl In menu.Controls
If Ctrl.Type = msoControlButton Then
num = num + 1
Cells(num + 1, 1) = menu.Caption
Cells(num + 1, 3) = Ctrl.Caption
Cells(num + 1, 4) = Ctrl.Id
Cells(num + 1, 5) = Ctrl.FaceId
Cells(num + 1, 6) = Ctrl.ShortcutText
ElseIf Ctrl.Type = msoControlPopup Then
num = num + 1
Cells(num + 1, 1) = menu.Caption
Cells(num + 1, 2) = Ctrl.Caption
Cells(num + 1, 4) = Ctrl.Id
Cells(num + 1, 5) = Ctrl.FaceId
For Each ctrlb In Ctrl.Controls
num = num + 1
Cells(num + 1, 1) = menu.Caption
Cells(num + 1, 2) = Ctrl.Caption
Cells(num + 1, 3) = ctrlb.Caption
Cells(num + 1, 4) = ctrlb.Id
Cells(num + 1, 5) = ctrlb.FaceId
Cells(num + 1, 6) = ctrlb.ShortcutText
Next
End If
Next
End Select
Next
Cells(3, 2) = Application.WorksheetFunction.CountA(Range("c7:c" & Range("c65536").End(xlUp).Row))
Cells.Select
Selection.Rows.AutoFit
Selection.Columns.AutoFit
Range("A1").Select
MsgBox "Un nouveau classeur a été crée." & Chr(13) & _
"Il contient les informations demandées", , "kalobit vous informe"
Application.StatusBar = False
Application.ScreenUpdating = True
Application.CommandBars(CBIndex).Visible = True
End Sub
à appeler avec le numero de la barre d'outil ou son nom de code.
++++
[mailto:K@lobit K@lobit]