Posez votre question Liste des messages Discussion Liste des forums Rechercher Audio

[bob87]

Bonjour,

j'ai crée une menu sur ma feuille excel avec un code VBA et je voudrais créer d'autres en les espaçants de la sorte :

Mon menu
--Quiter
--Annuler
--------------
--Ouvrir
--Aditionner
--Soustraire

Voila mon code çi dessus. Pouvez vous m'aider?

Private Sub Workbook_Open()

addCQTMToolbar
'addCQTMToolbar1
End Sub

Private Sub addCQTMToolbar()

Const menuName = "Mon menu "

Dim menuItems(1, 1) As String
Dim value As Variant
Dim i As Integer

Dim myMenuBar As CommandBar
Dim newMenu As CommandBarControl
Dim ctrl1 As CommandBarControl


menuItems(0, 0) = "Quiter.."
menuItems(1, 0) = "Annuler"


On Error Resume Next
Set myMenuBar = Application.CommandBars.ActiveMenuBar
myMenuBar.Controls(menuName).Delete
Set newMenu = myMenuBar.Controls.Add(msoControlPopup, , , 10, True)
newMenu.Caption = menuName

For i = 0 To UBound(menuItems, 1)
Set ctrl1 = newMenu.Controls.Add(msoControlButton, i + 1)
ctrl1.Caption = menuItems(i, 0)
ctrl1.TooltipText = menuItems(i, 0)
ctrl1.Style = msoButtonCaption
ctrl1.OnAction = menuItems(i, 1)
Next

Err.Clear

End Sub

Merci bien

 

[PMO2]

Re : Posez votre question Liste des messages Discussion Liste des forums Rechercher A

Bonjour,

Inspirez vous du code de Stephen Bullen

Public Sub AddCustomMenu()
   Dim cbWSMenuBar As CommandBar
   Dim muCustom As CommandBarControl
   Dim iHelpIndex As Integer

   Set cbWSMenuBar = Application.CommandBars("Worksheet Menu Bar")
   iHelpIndex = cbWSMenuBar.Controls("?").Index
   Set muCustom = cbWSMenuBar.Controls.Add(Type:=msoControlPopup, _
                                                Before:=iHelpIndex)
   With muCustom
      .Caption = "&Custom"
      With .Controls.Add(Type:=msoControlButton)
         .Caption = "&Show Data Form"
         .OnAction = "ShowDataForm"
      End With
      With .Controls.Add(Type:=msoControlButton)
         .Caption = "&Print Data List"
         .OnAction = "PrintDataList"
      End With
      With .Controls.Add(Type:=msoControlButton)
         .Caption = "Sort Names &Ascending"
         .BeginGroup = True
         .OnAction = "SortList"
         .Parameter = "Asc"
      End With
      With .Controls.Add(Type:=msoControlButton)
         .Caption = "Sort Names &Descending"
         .OnAction = "SortList"
         .Parameter = "Dsc"
      End With
      With .Controls.Add(Type:=msoControlButton)
         .Caption = "Show Products"
'         .OnAction = "ShowProduct"
'         .OnAction = "ShowProduct ""Apple"", 3, 4"
          .OnAction = "'ShowProduct ""Apple"", 3, 4'"
          '.OnAction = "'ShowProduct """ 'ShowProduct ""Apple"","", 3, 4'"4'"
      End With
   End With
End Sub


Public Sub RemoveCustomMenu()
   Dim cbWSMenuBar As CommandBar
   On Error Resume Next
   Set cbWSMenuBar = CommandBars("Worksheet Menu Bar")
   cbWSMenuBar.Controls("Custom").Delete
End Sub

Cordialement.

PMO
Patrick Morange