1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51
| Const MENU_CONTEXT_CAPTION As String = "Choisir un mois..."
Dim Mois As Variant 'portée Module
Sub MenuContextuel(Optional dummy As Byte)
Dim BAR As CommandBar
Dim C As CommandBarControl
Dim CB As CommandBarComboBox
Dim i&
'---
Mois = Array("Janvier", "Février", "Mars", "Avril", "Mai", "Juin", "Juillet", "Août", "Septembre", "Octobre", "Novembre", "Décembre")
Call DeleteMenuContextuel
Set BAR = Application.CommandBars("Ply")
Set C = BAR.Controls.Add(Type:=msoControlPopup, Before:=1, Temporary:=True)
With C
.Caption = MENU_CONTEXT_CAPTION
.Tag = "My_Cell_Control_Tag"
For i& = LBound(Mois) To UBound(Mois)
With .Controls.Add(Type:=msoControlButton)
.Caption = Mois(i&)
.OnAction = "'NomFeuille " & i& & "'"
End With
Next i&
End With
BAR.Controls(2).BeginGroup = True
End Sub
Sub DeleteMenuContextuel(Optional dummy As Byte)
On Error Resume Next
Application.CommandBars("Ply").Controls(MENU_CONTEXT_CAPTION).Delete
End Sub
Sub NomFeuille(Index As Integer)
Dim S As Worksheet
Dim A$
Dim i&
A$ = Mois(Index)
If ActiveSheet.Name = A$ Then Exit Sub
On Error Resume Next
Set S = ThisWorkbook.Sheets(A$)
If Err = 0 Then
Do
Err.Clear
i& = i& + 1
A$ = Mois(Index) & Format(i&, "00")
Set S = ThisWorkbook.Sheets(A$)
Loop Until Err <> 0
End If
Err.Clear
ActiveSheet.Name = A$
End Sub |
Partager