Public cb As CommandBar
Sub cre_menu()
Dim liste As Range
Set liste = Range("liste")
If liste.Column > 1 Then Exit Sub
Dim i As Long, nbl As Long
On Error Resume Next
CommandBars("Menu_Gw").Delete
On Error GoTo 0
Set cb = Application.CommandBars.Add("Menu_Gw", msoBarPopup)
nbl = liste.Count
For i = 1 To nbl
With cb.Controls.Add(msoControlButton, 1, , , True)
.Caption = liste(i)
.OnAction = "gw_lance(" & i & ")"
End With
Next i
cb.ShowPopup
End Sub
Sub cre_menu_1()
Dim liste As Range
Set liste1 = Range("liste1")
If liste1.Column > 2 Then Exit Sub
Dim i As Long, nbl1 As Long
On Error Resume Next
CommandBars("Menu_Gw").Delete
On Error GoTo 0
Set cb = Application.CommandBars.Add("Menu_Gw", msoBarPopup)
nbl1 = liste1.Count
For i = 1 To nbl1
With cb.Controls.Add(msoControlButton, 1, , , True)
.Caption = liste1(i)
.OnAction = "gw_lance(" & i & ")"
End With
Next i
cb.ShowPopup
End Sub
Sub cre_menu_2()
Dim liste As Range
Set liste2 = Range("liste2")
If liste2.Column > 3 Then Exit Sub
Dim i As Long, nbl2 As Long
On Error Resume Next
CommandBars("Menu_Gw").Delete
On Error GoTo 0
Set cb = Application.CommandBars.Add("Menu_Gw", msoBarPopup)
nbl2 = liste2.Count
For i = 1 To nbl2
With cb.Controls.Add(msoControlButton, 1, , , True)
.Caption = liste2(i)
.OnAction = "gw_lance(" & i & ")"
End With
Next i
cb.ShowPopup
End Sub
Sub gw_lance(index As Long)
ActiveCell = cb.Controls(index).Caption
End Sub