ComboBox in Command Bar

[oleneazer]

Hello----------------------(I'am french so excuse my poor english )
I try to make a personal toolbar wich contains tow comboBox
one to display days of the current month and the other one to display months of the current year

Here the code that i put in a standard module call by Workbook_Open

Sub Testbar() 
Dim objPopUp As CommandBarPopup
Dim objButton As CommandBarButton
Dim iMois, iJour As Integer
Dim cb As CommandBar
Dim graphBtn As CommandBarButton
Set cb = CommandBars.Add(Name:="test", Position:=msoBarTop)
cb.Visible = True
Set objPopUp = cb.Controls.Add(msoControlPopup)
objPopUp.Caption = "Total-Jour de: " & UCase(Format(DateSerial(1, Month(Date), 1), "mmmm"))
For iJour = 1 To Day(DateSerial(Year(Date), Month(Date) + 1, 0))
Set objButton = objPopUp.Controls.Add
With objButton
.Caption = Format(DateSerial(Year(Date), Month(Date), iJour), "dddd dd")
.OnAction = "macro1"
.Style = msoButtonCaption
End With
Next iJour
Set objPopUp = cb.Controls.Add(msoControlPopup)
objPopUp.Caption = "TOTAL Mensuel"
For iMois = 1 To 12
Set objButton = objPopUp.Controls.Add
With objButton
.Caption = Application.WorksheetFunction.Proper(Format(DateSerial(1, iMois, 1), "mmmm"))
.OnAction = "Macro2"
.Style = msoButtonCaption
.BeginGroup = True
End With
Next iJour
End sub

I want change the code to have a menu in the toolbar with twos submenus one for the day and one for the months. Somebody can help me?

[smozgur]

Hi oleneazer,

I am not sure if this post is in the right forum. Did you want to make this function a kb entry or is this your Excel question ?

Both way, following modification in your code might help.

Sub Testbar()
    Dim objMainPopUp As CommandBarPopup
    Dim objPopUp As CommandBarPopup
    Dim objButton As CommandBarButton
    Dim iMois, iJour As Integer
    Dim cb As CommandBar
    Dim graphBtn As CommandBarButton
    Set cb = CommandBars.Add(Name:="test", Position:=msoBarTop)
    cb.Visible = True
    Set objMainPopUp = cb.Controls.Add(msoControlPopup)
    objMainPopUp.Caption = "Total"
Set objPopUp = objMainPopUp.Controls.Add(msoControlPopup)
    objPopUp.Caption = "Total-Jour de: " & UCase(Format(DateSerial(1, Month(Date), 1), "mmmm"))
    For iJour = 1 To Day(DateSerial(Year(Date), Month(Date) + 1, 0))
        Set objButton = objPopUp.Controls.Add
        With objButton
            .Caption = Format(DateSerial(Year(Date), Month(Date), iJour), "dddd dd")
            .OnAction = "macro1"
            .Style = msoButtonCaption
        End With
    Next iJour
Set objPopUp = objMainPopUp.Controls.Add(msoControlPopup)
    objPopUp.Caption = "TOTAL Mensuel"
    For iMois = 1 To 12
        Set objButton = objPopUp.Controls.Add
        With objButton
            .Caption = Application.WorksheetFunction.Proper(Format(DateSerial(1, iMois, 1), "mmmm"))
            .OnAction = "Macro2"
            .Style = msoButtonCaption
            .BeginGroup = True
        End With
    Next iMois
End Sub

I hope it helps.
Suat

[Anne Troy]

Hi! I moved your thread to the appropriate forum, oleneazer.
vbmenu_register("postmenu_7824", true);

[oleneazer]

It is exactly the result i want to obtain
Thanks for your help

[smozgur]

You're welcome!

Suat