Creating a custom toolbar add-in in Excel 2007

[test_52]

I have been able to create a custom toolbar add-in using vba in Excel 2007
from different examples that I have found on this site. The problem I am
running into is the visual layout of the toolbar. My drop down menu buttons
stack vertically instead of horizontally. Is there any way to change this? I
have a custom add-in that I found on the web that I had used in Excel 2003
and when I install it in 2007, the buttons are displayed horizontally. I
can't figure out where in the programming this is done.

I have attached some of my code that adds the drop down menus below. Am I
doing something wrong? Any help would be great! Thanks, Jason


Dim cmdBar As CommandBar
Dim cmdBarMenu As CommandBarControl
Dim cmdBarMenuItem As CommandBarControl


'drop down - Comments
Set cmdBar = Application.CommandBars("Worksheet Menu Bar")
Set cmdBarMenu = cmdBar.Controls.Add(Type:=msoControlPopup,
before:=cmdBar.Controls("Window").Index)
cmdBarMenu.Caption = "Comments"
cmdBarMenu.Tag = "JJT"

Set cmdBarMenuItem = cmdBarMenu.Controls.Add
With cmdBarMenuItem
.Caption = "Create rounded comment"
.OnAction = "RoundedComment"
.Tag = "JJT"
.FaceId = 588
End With

Set cmdBarMenuItem = cmdBarMenu.Controls.Add
With cmdBarMenuItem
.Caption = "Change comment color"
.OnAction = "ChangeCommentColor"
.Tag = "JJT"
.FaceId = 1691
End With



'drop down - Formatting
Set cmdBar = Application.CommandBars("Worksheet Menu Bar")
Set cmdBarMenu = cmdBar.Controls.Add(Type:=msoControlPopup,
before:=cmdBar.Controls("Window").Index)
cmdBarMenu.Caption = "Formatting"
cmdBarMenu.Tag = "JJT"
Set cmdBarMenuItem = cmdBarMenu.Controls.Add
With cmdBarMenuItem
.Caption = "###°"
.OnAction = "DegreesFormat"
.Tag = "JJT"
End With
Set cmdBarMenuItem = cmdBarMenu.Controls.Add
With cmdBarMenuItem
.Caption = "## ###/###"
.OnAction = "HPFormat"
.Tag = "JJT"
End With
Set cmdBarMenuItem = cmdBarMenu.Controls.Add
With cmdBarMenuItem
.Caption = "## ##/##â€"
.OnAction = "InchesFractionalFormat"
.Tag = "JJT"
End With
Set cmdBarMenuItem = cmdBarMenu.Controls.Add
With cmdBarMenuItem
.Caption = "#,###"
.OnAction = "NumberCommaFormat"
.Tag = "JJT"
End With
Set cmdBarMenuItem = cmdBarMenu.Controls.Add
With cmdBarMenuItem
.Caption = "# ##/##â€Ã˜"
.OnAction = "DiameterInchesFractionsFormat"
.Tag = "JJT"
End With
Set cmdBarMenuItem = cmdBarMenu.Controls.Add
With cmdBarMenuItem
.Caption = "##â€Ã˜"
.OnAction = "DiameterInchesFormat"
.Tag = "JJT"
End With
Set cmdBarMenuItem = cmdBarMenu.Controls.Add
With cmdBarMenuItem
.Caption = "##.#"
.OnAction = "GPMFormat"
.Tag = "JJT"
End With

 

[Jim Rech]

We don't have any direct control over how Excel puts our commandbar
customizations on the Add-in tab of the ribbon. It seems to add them to the
Menu Commands group of the Add-ins tab in the order they are loaded/created.
It stacks the first three and then starts a new stack of up to three, etc.

I suppose if you added two bogus/empty items to your code, between the two
you have, that would force your second, now fourth, item up next to the
first. That assumes nothing else it being added before your customizations
so that your first is first in a stack.

--
Jim
|I have been able to create a custom toolbar add-in using vba in Excel 2007
| from different examples that I have found on this site. The problem I am
| running into is the visual layout of the toolbar. My drop down menu
buttons
| stack vertically instead of horizontally. Is there any way to change this?
I
| have a custom add-in that I found on the web that I had used in Excel 2003
| and when I install it in 2007, the buttons are displayed horizontally. I
| can't figure out where in the programming this is done.
|
| I have attached some of my code that adds the drop down menus below. Am I
| doing something wrong? Any help would be great! Thanks, Jason
|
|
| Dim cmdBar As CommandBar
| Dim cmdBarMenu As CommandBarControl
| Dim cmdBarMenuItem As CommandBarControl
|
|
| 'drop down - Comments
| Set cmdBar = Application.CommandBars("Worksheet Menu Bar")
| Set cmdBarMenu = cmdBar.Controls.Add(Type:=msoControlPopup,
| before:=cmdBar.Controls("Window").Index)
| cmdBarMenu.Caption = "Comments"
| cmdBarMenu.Tag = "JJT"
|
| Set cmdBarMenuItem = cmdBarMenu.Controls.Add
| With cmdBarMenuItem
| .Caption = "Create rounded comment"
| .OnAction = "RoundedComment"
| .Tag = "JJT"
| .FaceId = 588
| End With
|
| Set cmdBarMenuItem = cmdBarMenu.Controls.Add
| With cmdBarMenuItem
| .Caption = "Change comment color"
| .OnAction = "ChangeCommentColor"
| .Tag = "JJT"
| .FaceId = 1691
| End With
|
|
|
| 'drop down - Formatting
| Set cmdBar = Application.CommandBars("Worksheet Menu Bar")
| Set cmdBarMenu = cmdBar.Controls.Add(Type:=msoControlPopup,
| before:=cmdBar.Controls("Window").Index)
| cmdBarMenu.Caption = "Formatting"
| cmdBarMenu.Tag = "JJT"
| Set cmdBarMenuItem = cmdBarMenu.Controls.Add
| With cmdBarMenuItem
| .Caption = "###°"
| .OnAction = "DegreesFormat"
| .Tag = "JJT"
| End With
| Set cmdBarMenuItem = cmdBarMenu.Controls.Add
| With cmdBarMenuItem
| .Caption = "## ###/###"
| .OnAction = "HPFormat"
| .Tag = "JJT"
| End With
| Set cmdBarMenuItem = cmdBarMenu.Controls.Add
| With cmdBarMenuItem
| .Caption = "## ##/##""
| .OnAction = "InchesFractionalFormat"
| .Tag = "JJT"
| End With
| Set cmdBarMenuItem = cmdBarMenu.Controls.Add
| With cmdBarMenuItem
| .Caption = "#,###"
| .OnAction = "NumberCommaFormat"
| .Tag = "JJT"
| End With
| Set cmdBarMenuItem = cmdBarMenu.Controls.Add
| With cmdBarMenuItem
| .Caption = "# ##/##"Ø"
| .OnAction = "DiameterInchesFractionsFormat"
| .Tag = "JJT"
| End With
| Set cmdBarMenuItem = cmdBarMenu.Controls.Add
| With cmdBarMenuItem
| .Caption = "##"Ø"
| .OnAction = "DiameterInchesFormat"
| .Tag = "JJT"
| End With
| Set cmdBarMenuItem = cmdBarMenu.Controls.Add
| With cmdBarMenuItem
| .Caption = "##.#"
| .OnAction = "GPMFormat"
| .Tag = "JJT"
| End With
|
|
|
|
|