Nom anglais des barres de menus et des controls excel
2 réponses
patrice-toulouse
où pourai je troubver la liste en anglais de l'ensemble des menus sous menus
et contrôle. J'ai trouver la procédure suivante pour les lister mais elle me
les donne qu'en français .. Je ne peux pas me servir de ces noms pour
désactiver un sous menu par example.
Procédure à insérer dans le code d'une feuille vide
Option Explicit
Sub ListAllControls()
Dim cbBar As CommandBar
Dim Rng As Range
Dim cbCtl As CommandBarControl
If Not IsEmptyWorksheet(ActiveSheet) Then Exit Sub
Application.ScreenUpdating = False
Set Rng = Range("A1")
For Each cbBar In Application.CommandBars
Application.StatusBar = "Processing Bar " & cbBar.Name
Rng.Value = cbBar.Name
For Each cbCtl In cbBar.Controls
Set Rng = Rng.Offset(ListControls(cbCtl, Rng))
Next cbCtl
Next cbBar
Range("A:I").EntireColumn.AutoFit
Application.StatusBar = False
End Sub
Function ListControls(cbCtl As CommandBarControl, Rng As Range) As Long
Dim lOffset As Long 'Tracks current row relative to Rng
Dim ctlSub As CommandBarControl 'Control contained in cbCtl
On Error Resume Next
lOffset = 0
Rng.Offset(lOffset, 1).Value = cbCtl.Caption
Rng.Offset(lOffset, 2).Value = cbCtl.Type
'Attempt to copy control face. If error, don't paste
cbCtl.CopyFace
If Err.Number = 0 Then
ActiveSheet.Paste Rng.Offset(lOffset, 3)
Rng.Offset(lOffset, 3).Value = cbCtl.FaceId
End If
Err.Clear
'Check Control Type
Select Case cbCtl.Type
Case 1, 2, 4, 6, 7, 13, 18
'Do nothing for these control types
Case Else
'Call function recursively if current control contains other controls
For Each ctlSub In cbCtl.Controls
lOffset = lOffset + _
ListControls(ctlSub, Rng.Offset(lOffset, 2))
Next ctlSub
lOffset = lOffset - 1
End Select
ListControls = lOffset + 1
End Function
Function IsEmptyWorksheet(Sht As Object) As Boolean
If TypeName(Sht) = "Worksheet" Then
If WorksheetFunction.CountA(Sht.UsedRange) = 0 Then
IsEmptyWorksheet = True
Exit Function
End If
End If
MsgBox "Please make sure that an empty worksheet is active"
End Function