I wish to use VB in Excel to create an option entry in a menu that I have
created. For example, MS have done this with viewing the Formula Bar under
the View menu. Suggestions welcome, many thanks in advance.

Hi,

Do you mean the little tick/checkmark next to the menu item? If so you
need to set the .State property. You then change the State property
every time the button is clicked i.e. in the OnAction procedure.

See below,
HTH.

Sub createmycommandbar()

'-snippet

With NewSubItem
.Caption = "&Include Unapproved"
.OnAction = "fcnToggleIncludeUnapproved"
.Style = msoButtonCaption
.State = msoButtonDown
.TooltipText = "Click me"
.Tag = "SUPERUSERS,WRITEACCESSUSERS"
.BeginGroup = True
End With
'-end snippet

End Sub

Function fcnToggleIncludeUnapproved() As Boolean

Dim mySubItem As CommandBarControl

On Error GoTo RecreateCommandBar
Set mySubItem = _
Application.CommandBars(TOOLBAR_NAME) _
.Controls("Reports").Controls("Include Unapproved")
mySubItem.State = Not msoButtonUp

Set mySubItem = Nothing
fcnToggleIncludeUnapproved = True
Exit Function

RecreateCommandBar:
fcnCommandBarLoad 'this also applies the security
Resume

End Function

When I do this I actually store the state as a global boolean - just in
case I need to rebuild the command bar for some reason, I don't want the
status to reset.


Scott KBC wrote:
I wish to use VB in Excel to create an option entry in a menu that I have
created. For example, MS have done this with viewing the Formula Bar under
the View menu. Suggestions welcome, many thanks in advance.

Gareth

Thanks for this, I got most of the way there. I created my menu item, set
the style and a default state. However, in the third macro included here
(UsePIPPrices), it doesn't like me setting the state. Am I doing something
wrong? Could it be Excel version / SP related?

Problem line:
Itemj.State = msoButtonUp

Many thanks...

Sub Make_Menus()

Dim ClearMenu, ImportMenu, PriceMenu, StoreMenu

' Configure the Tracker Menu and point all menu options to run macros in
this workbook.
' This overwrites any existing Tracker Menu Bar.

MenuBars(xlWorksheet).Menus.Add Caption:="OPC Tracker"
With MenuBars(xlWorksheet).Menus("OPC Tracker")
Set ClearMenu = .MenuItems.AddMenu(Caption:="Clear Data")
ClearMenu.MenuItems.Add Caption:="Clear Prices",
OnAction:="ClearPrices"
ClearMenu.MenuItems.Add Caption:="Clear Yields",
OnAction:="ClearYields"
ClearMenu.MenuItems.Add Caption:="Clear OPC Data",
OnAction:="ClearOPCs"
ClearMenu.MenuItems.Add Caption:="Clear All Data",
OnAction:="ClearBook"
Set ImportMenu = .MenuItems.AddMenu(Caption:="Import Data")
ImportMenu.MenuItems.Add Caption:="Import Prices",
OnAction:="DoMigratePrices"
ImportMenu.MenuItems.Add Caption:="Import Yields",
OnAction:="DoMigrateYields"
ImportMenu.MenuItems.Add Caption:="Import OPC Data",
OnAction:="DoMigrateOPCs"
ImportMenu.MenuItems.Add Caption:="Import All Data",
OnAction:="DoMigrate"
.MenuItems.Add Caption:="-"
.MenuItems.Add Caption:="Populate Process Unit OPC Sheets",
OnAction:="Populate_Book"
.MenuItems.Add Caption:="-"
Set StoreMenu = .MenuItems.AddMenu(Caption:="Store OPC Data")
StoreMenu.MenuItems.Add Caption:="Store OPC Data for This Process
Unit", OnAction:="Store_Data"
StoreMenu.MenuItems.Add Caption:="Store OPC Data for All Process
Units", OnAction:="Store_All"
Set PriceMenu = .MenuItems.AddMenu(Caption:="Prices")
PriceMenu.MenuItems.Add Caption:="Archive Prices to Historian",
OnAction:="HistWriteData"
PriceMenu.MenuItems.Add Caption:="Retrieve Prices from Historian",
OnAction:="HistGetData"
Set UsePIPItem = .MenuItems.Add(Caption:="Use PIP Prices")
With UsePIPItem
Caption = "Use PIP Prices"
OnAction = "UsePIPPrices"
Style = msoButtonCaption
State = msoButtonDown
End With
End With

' Activate or deactivate certain menu items based upon the active sheet.
Shade_Menu
'Enable_Tracker_MenuItems

End Sub


Sub UsePIPPrices()

' Handle the menu option to use PIP Prices.
For Each Itemj In MenuBars(xlWorksheet).Menus("OPC Tracker").MenuItems
If LCase(Itemj.Caption) Like "*use pip prices*" Then
If Itemj.State = msoButtonDown Then
Range("Yields_UsePIPPrices") = "No"
Itemj.State = msoButtonUp
Else
Range("Yields_UsePIPPrices") = "Yes"
Itemj.State = msoButtonDown
End If
End If
Next Itemj

End Sub


Sub Shade_Menu()

Dim OppLossAddress As String, MBErrorAddress As String
Dim Itemj

' Activate or deactivate certain menu items based upon the active sheet.
With ActiveSheet
OppLossAddress = Empty
MBErrorAddress = Empty
On Error Resume Next
OppLossAddress = .Range("Opp_Loss").address
MBErrorAddress = .Range("MB_Error").address

If OppLossAddress = Empty And MBErrorAddress = Empty Then
For Each Itemj In MenuBars(xlWorksheet).Menus("OPC
Tracker").MenuItems
If LCase(Itemj.Caption) Like "*store opc*" Then
Itemj.Enabled = False
Next Itemj
Else
For Each Itemj In MenuBars(xlWorksheet).Menus("OPC
Tracker").MenuItems
If LCase(Itemj.Caption) Like "*store opc*" Then
Itemj.Enabled = True
Next Itemj
End If
On Error GoTo 0
End With

For Each Itemj In MenuBars(xlWorksheet).Menus("OPC Tracker").MenuItems
If LCase(Itemj.Caption) Like "*use pip prices*" Then
If LCase(Range("Yields_UsePIPPrices")) Like "*y*" Then
Itemj.State = msoButtonDown
Else
Itemj.State = msoButtonUp
End If
End If
Next Itemj

End Sub

"Gareth" wrote:

Hi,

Do you mean the little tick/checkmark next to the menu item? If so you
need to set the .State property. You then change the State property
every time the button is clicked i.e. in the OnAction procedure.

See below,
HTH.

Sub createmycommandbar()

'-snippet

With NewSubItem
.Caption = "&Include Unapproved"
.OnAction = "fcnToggleIncludeUnapproved"
.Style = msoButtonCaption
.State = msoButtonDown
.TooltipText = "Click me"
.Tag = "SUPERUSERS,WRITEACCESSUSERS"
.BeginGroup = True
End With
'-end snippet

End Sub

Function fcnToggleIncludeUnapproved() As Boolean

Dim mySubItem As CommandBarControl

On Error GoTo RecreateCommandBar
Set mySubItem = _
Application.CommandBars(TOOLBAR_NAME) _
.Controls("Reports").Controls("Include Unapproved")
mySubItem.State = Not msoButtonUp

Set mySubItem = Nothing
fcnToggleIncludeUnapproved = True
Exit Function

RecreateCommandBar:
fcnCommandBarLoad 'this also applies the security
Resume

End Function

When I do this I actually store the state as a global boolean - just in
case I need to rebuild the command bar for some reason, I don't want the
status to reset.


Scott KBC wrote:
I wish to use VB in Excel to create an option entry in a menu that I have
created. For example, MS have done this with viewing the Formula Bar under
the View menu. Suggestions welcome, many thanks in advance.

Hi Scott,

I see you're using MenuBars. I've never used them. I used Commandbars instead. I think that's the more current method but I could be wrong. Here's an (edited) example of code I use. Maybe someone else
can suggest a method of achieving what you want using MenuBars.

I notice you didn't declare your variable types specifically. If you do this (I have declared CommandBarControl types here) you will find it easy to see the available properties and methods (when you
enter the "."). Note that in this particular case, for the state property, you don't see it - but in general it's useful.

HTH,
Gareth

Function fcnCommandBarLoad() As Boolean

Dim NewItem As CommandBarControl
Dim NewSubItem As CommandBarControl

On Error GoTo Errorhandler_ToolbarExists
Application.CommandBars.Add(Name:=TOOLBAR_NAME, _
Position:=msoBarTop, Temporary:=True).Visible = True
On Error GoTo 0

With Application.CommandBars(TOOLBAR_NAME)

Set NewItem = .Controls.Add(Type:=msoControlButton)
With NewItem
.Caption = "&Open Records"
.OnAction = "OpenRecords"
.Style = msoButtonIcon
.BeginGroup = True
.FaceId = 620
.Tag = "ALLUSERS"
End With

Set NewItem = .Controls.Add(Type:=msoControlButton)
With NewItem
.Caption = "Edit &Records(s)"
.OnAction = "EditRecord"
.Style = msoButtonCaption
.BeginGroup = True
.TooltipText = "Edit ....."
.Tag = "ALLUSERS,ACTIVEONLY"
End With
'more buttons as necessary

End With

fcnCommandBarApplySecurity

fcnCommandBarLoad = True

'Skip ErrorHandling Section
Exit Function

Errorhandler_ToolbarExists:
' Let's delete it and rebuild....
Application.CommandBars(TOOLBAR_NAME).Delete
Resume

End Function


Scott KBC wrote:
Gareth

Thanks for this, I got most of the way there. I created my menu item, set
the style and a default state. However, in the third macro included here
(UsePIPPrices), it doesn't like me setting the state. Am I doing something
wrong? Could it be Excel version / SP related?

Problem line:
Itemj.State = msoButtonUp

Many thanks...

Sub Make_Menus()

Dim ClearMenu, ImportMenu, PriceMenu, StoreMenu

' Configure the Tracker Menu and point all menu options to run macros in
this workbook.
' This overwrites any existing Tracker Menu Bar.

MenuBars(xlWorksheet).Menus.Add Caption:="OPC Tracker"
With MenuBars(xlWorksheet).Menus("OPC Tracker")
Set ClearMenu = .MenuItems.AddMenu(Caption:="Clear Data")
ClearMenu.MenuItems.Add Caption:="Clear Prices",
OnAction:="ClearPrices"
ClearMenu.MenuItems.Add Caption:="Clear Yields",
OnAction:="ClearYields"
ClearMenu.MenuItems.Add Caption:="Clear OPC Data",
OnAction:="ClearOPCs"
ClearMenu.MenuItems.Add Caption:="Clear All Data",
OnAction:="ClearBook"
Set ImportMenu = .MenuItems.AddMenu(Caption:="Import Data")
ImportMenu.MenuItems.Add Caption:="Import Prices",
OnAction:="DoMigratePrices"
ImportMenu.MenuItems.Add Caption:="Import Yields",
OnAction:="DoMigrateYields"
ImportMenu.MenuItems.Add Caption:="Import OPC Data",
OnAction:="DoMigrateOPCs"
ImportMenu.MenuItems.Add Caption:="Import All Data",
OnAction:="DoMigrate"
.MenuItems.Add Caption:="-"
.MenuItems.Add Caption:="Populate Process Unit OPC Sheets",
OnAction:="Populate_Book"
.MenuItems.Add Caption:="-"
Set StoreMenu = .MenuItems.AddMenu(Caption:="Store OPC Data")
StoreMenu.MenuItems.Add Caption:="Store OPC Data for This Process
Unit", OnAction:="Store_Data"
StoreMenu.MenuItems.Add Caption:="Store OPC Data for All Process
Units", OnAction:="Store_All"
Set PriceMenu = .MenuItems.AddMenu(Caption:="Prices")
PriceMenu.MenuItems.Add Caption:="Archive Prices to Historian",
OnAction:="HistWriteData"
PriceMenu.MenuItems.Add Caption:="Retrieve Prices from Historian",
OnAction:="HistGetData"
Set UsePIPItem = .MenuItems.Add(Caption:="Use PIP Prices")
With UsePIPItem
Caption = "Use PIP Prices"
OnAction = "UsePIPPrices"
Style = msoButtonCaption
State = msoButtonDown
End With
End With

' Activate or deactivate certain menu items based upon the active sheet.
Shade_Menu
'Enable_Tracker_MenuItems

End Sub


Sub UsePIPPrices()

' Handle the menu option to use PIP Prices.
For Each Itemj In MenuBars(xlWorksheet).Menus("OPC Tracker").MenuItems
If LCase(Itemj.Caption) Like "*use pip prices*" Then
If Itemj.State = msoButtonDown Then
Range("Yields_UsePIPPrices") = "No"
Itemj.State = msoButtonUp
Else
Range("Yields_UsePIPPrices") = "Yes"
Itemj.State = msoButtonDown
End If
End If
Next Itemj

End Sub


Sub Shade_Menu()

Dim OppLossAddress As String, MBErrorAddress As String
Dim Itemj

' Activate or deactivate certain menu items based upon the active sheet.
With ActiveSheet
OppLossAddress = Empty
MBErrorAddress = Empty
On Error Resume Next
OppLossAddress = .Range("Opp_Loss").address
MBErrorAddress = .Range("MB_Error").address

If OppLossAddress = Empty And MBErrorAddress = Empty Then
For Each Itemj In MenuBars(xlWorksheet).Menus("OPC
Tracker").MenuItems
If LCase(Itemj.Caption) Like "*store opc*" Then
Itemj.Enabled = False
Next Itemj
Else
For Each Itemj In MenuBars(xlWorksheet).Menus("OPC
Tracker").MenuItems
If LCase(Itemj.Caption) Like "*store opc*" Then
Itemj.Enabled = True
Next Itemj
End If
On Error GoTo 0
End With

For Each Itemj In MenuBars(xlWorksheet).Menus("OPC Tracker").MenuItems
If LCase(Itemj.Caption) Like "*use pip prices*" Then
If LCase(Range("Yields_UsePIPPrices")) Like "*y*" Then
Itemj.State = msoButtonDown
Else
Itemj.State = msoButtonUp
End If
End If
Next Itemj

End Sub

"Gareth" wrote:


Hi,

Do you mean the little tick/checkmark next to the menu item? If so you
need to set the .State property. You then change the State property
every time the button is clicked i.e. in the OnAction procedure.

See below,
HTH.

Sub createmycommandbar()

'-snippet

With NewSubItem
.Caption = "&Include Unapproved"
.OnAction = "fcnToggleIncludeUnapproved"
.Style = msoButtonCaption
.State = msoButtonDown
.TooltipText = "Click me"
.Tag = "SUPERUSERS,WRITEACCESSUSERS"
.BeginGroup = True
End With
'-end snippet

End Sub

Function fcnToggleIncludeUnapproved() As Boolean

Dim mySubItem As CommandBarControl

On Error GoTo RecreateCommandBar
Set mySubItem = _
Application.CommandBars(TOOLBAR_NAME) _
.Controls("Reports").Controls("Include Unapproved")
mySubItem.State = Not msoButtonUp

Set mySubItem = Nothing
fcnToggleIncludeUnapproved = True
Exit Function

RecreateCommandBar:
fcnCommandBarLoad 'this also applies the security
Resume

End Function

When I do this I actually store the state as a global boolean - just in
case I need to rebuild the command bar for some reason, I don't want the
status to reset.


Scott KBC wrote:

I wish to use VB in Excel to create an option entry in a menu that I have
created. For example, MS have done this with viewing the Formula Bar under
the View menu. Suggestions welcome, many thanks in advance.

Gareth

Many thanks for your help. I have been fiddling around with CommandBars,
getting a menu set up using these. It is difficult and much less intuitive,
and for the first time I am seeing the With... End With setup not working!
My biggest issue is getting stepped menus, for example Excel's Tools
Protection ... configuration.

I can, however, get the commandbuttons to work which was my original query -
I'm screwed either way it would seem.

Thanks again

Scott.

"Gareth" wrote:

Hi Scott,

I see you're using MenuBars. I've never used them. I used Commandbars instead. I think that's the more current method but I could be wrong. Here's an (edited) example of code I use. Maybe someone else
can suggest a method of achieving what you want using MenuBars.

I notice you didn't declare your variable types specifically. If you do this (I have declared CommandBarControl types here) you will find it easy to see the available properties and methods (when you
enter the "."). Note that in this particular case, for the state property, you don't see it - but in general it's useful.

HTH,
Gareth

Function fcnCommandBarLoad() As Boolean

Dim NewItem As CommandBarControl
Dim NewSubItem As CommandBarControl

On Error GoTo Errorhandler_ToolbarExists
Application.CommandBars.Add(Name:=TOOLBAR_NAME, _
Position:=msoBarTop, Temporary:=True).Visible = True
On Error GoTo 0

With Application.CommandBars(TOOLBAR_NAME)

Set NewItem = .Controls.Add(Type:=msoControlButton)
With NewItem
.Caption = "&Open Records"
.OnAction = "OpenRecords"
.Style = msoButtonIcon
.BeginGroup = True
.FaceId = 620
.Tag = "ALLUSERS"
End With

Set NewItem = .Controls.Add(Type:=msoControlButton)
With NewItem
.Caption = "Edit &Records(s)"
.OnAction = "EditRecord"
.Style = msoButtonCaption
.BeginGroup = True
.TooltipText = "Edit ....."
.Tag = "ALLUSERS,ACTIVEONLY"
End With
'more buttons as necessary

End With

fcnCommandBarApplySecurity

fcnCommandBarLoad = True

'Skip ErrorHandling Section
Exit Function

Errorhandler_ToolbarExists:
' Let's delete it and rebuild....
Application.CommandBars(TOOLBAR_NAME).Delete
Resume

End Function


Scott KBC wrote:
Gareth

Thanks for this, I got most of the way there. I created my menu item, set
the style and a default state. However, in the third macro included here
(UsePIPPrices), it doesn't like me setting the state. Am I doing something
wrong? Could it be Excel version / SP related?

Problem line:
Itemj.State = msoButtonUp

Many thanks...

Sub Make_Menus()

Dim ClearMenu, ImportMenu, PriceMenu, StoreMenu

' Configure the Tracker Menu and point all menu options to run macros in
this workbook.
' This overwrites any existing Tracker Menu Bar.

MenuBars(xlWorksheet).Menus.Add Caption:="OPC Tracker"
With MenuBars(xlWorksheet).Menus("OPC Tracker")
Set ClearMenu = .MenuItems.AddMenu(Caption:="Clear Data")
ClearMenu.MenuItems.Add Caption:="Clear Prices",
OnAction:="ClearPrices"
ClearMenu.MenuItems.Add Caption:="Clear Yields",
OnAction:="ClearYields"
ClearMenu.MenuItems.Add Caption:="Clear OPC Data",
OnAction:="ClearOPCs"
ClearMenu.MenuItems.Add Caption:="Clear All Data",
OnAction:="ClearBook"
Set ImportMenu = .MenuItems.AddMenu(Caption:="Import Data")
ImportMenu.MenuItems.Add Caption:="Import Prices",
OnAction:="DoMigratePrices"
ImportMenu.MenuItems.Add Caption:="Import Yields",
OnAction:="DoMigrateYields"
ImportMenu.MenuItems.Add Caption:="Import OPC Data",
OnAction:="DoMigrateOPCs"
ImportMenu.MenuItems.Add Caption:="Import All Data",
OnAction:="DoMigrate"
.MenuItems.Add Caption:="-"
.MenuItems.Add Caption:="Populate Process Unit OPC Sheets",
OnAction:="Populate_Book"
.MenuItems.Add Caption:="-"
Set StoreMenu = .MenuItems.AddMenu(Caption:="Store OPC Data")
StoreMenu.MenuItems.Add Caption:="Store OPC Data for This Process
Unit", OnAction:="Store_Data"
StoreMenu.MenuItems.Add Caption:="Store OPC Data for All Process
Units", OnAction:="Store_All"
Set PriceMenu = .MenuItems.AddMenu(Caption:="Prices")
PriceMenu.MenuItems.Add Caption:="Archive Prices to Historian",
OnAction:="HistWriteData"
PriceMenu.MenuItems.Add Caption:="Retrieve Prices from Historian",
OnAction:="HistGetData"
Set UsePIPItem = .MenuItems.Add(Caption:="Use PIP Prices")
With UsePIPItem
Caption = "Use PIP Prices"
OnAction = "UsePIPPrices"
Style = msoButtonCaption
State = msoButtonDown
End With
End With

' Activate or deactivate certain menu items based upon the active sheet.
Shade_Menu
'Enable_Tracker_MenuItems

End Sub


Sub UsePIPPrices()

' Handle the menu option to use PIP Prices.
For Each Itemj In MenuBars(xlWorksheet).Menus("OPC Tracker").MenuItems
If LCase(Itemj.Caption) Like "*use pip prices*" Then
If Itemj.State = msoButtonDown Then
Range("Yields_UsePIPPrices") = "No"
Itemj.State = msoButtonUp
Else
Range("Yields_UsePIPPrices") = "Yes"
Itemj.State = msoButtonDown
End If
End If
Next Itemj

End Sub


Sub Shade_Menu()

Dim OppLossAddress As String, MBErrorAddress As String
Dim Itemj

' Activate or deactivate certain menu items based upon the active sheet.
With ActiveSheet
OppLossAddress = Empty
MBErrorAddress = Empty
On Error Resume Next
OppLossAddress = .Range("Opp_Loss").address
MBErrorAddress = .Range("MB_Error").address

If OppLossAddress = Empty And MBErrorAddress = Empty Then
For Each Itemj In MenuBars(xlWorksheet).Menus("OPC
Tracker").MenuItems
If LCase(Itemj.Caption) Like "*store opc*" Then
Itemj.Enabled = False
Next Itemj
Else
For Each Itemj In MenuBars(xlWorksheet).Menus("OPC
Tracker").MenuItems
If LCase(Itemj.Caption) Like "*store opc*" Then
Itemj.Enabled = True
Next Itemj
End If
On Error GoTo 0
End With

For Each Itemj In MenuBars(xlWorksheet).Menus("OPC Tracker").MenuItems
If LCase(Itemj.Caption) Like "*use pip prices*" Then
If LCase(Range("Yields_UsePIPPrices")) Like "*y*" Then
Itemj.State = msoButtonDown
Else
Itemj.State = msoButtonUp
End If
End If
Next Itemj

End Sub

"Gareth" wrote:


Hi,

Do you mean the little tick/checkmark next to the menu item? If so you
need to set the .State property. You then change the State property
every time the button is clicked i.e. in the OnAction procedure.

See below,
HTH.

Sub createmycommandbar()

'-snippet

With NewSubItem
.Caption = "&Include Unapproved"
.OnAction = "fcnToggleIncludeUnapproved"
.Style = msoButtonCaption
.State = msoButtonDown
.TooltipText = "Click me"
.Tag = "SUPERUSERS,WRITEACCESSUSERS"
.BeginGroup = True
End With
'-end snippet

End Sub

Function fcnToggleIncludeUnapproved() As Boolean

Dim mySubItem As CommandBarControl

On Error GoTo RecreateCommandBar
Set mySubItem = _
Application.CommandBars(TOOLBAR_NAME) _
.Controls("Reports").Controls("Include Unapproved")
mySubItem.State = Not msoButtonUp

Set mySubItem = Nothing
fcnToggleIncludeUnapproved = True
Exit Function

RecreateCommandBar:
fcnCommandBarLoad 'this also applies the security
Resume

End Function

When I do this I actually store the state as a global boolean - just in
case I need to rebuild the command bar for some reason, I don't want the
status to reset.


Scott KBC wrote:

I wish to use VB in Excel to create an option entry in a menu that I have
created. For example, MS have done this with viewing the Formula Bar under
the View menu. Suggestions welcome, many thanks in advance.

Hi Scott,

Stick with it. It's just because you're not familiar with it. I find CBs
far easier - because I'm used to them... And you can do as many submenus
as you need.

Below I've pasted code that demonstrates a submenu and a submenu of a
submenu as well as a working toggling, checkmark. And - would you
believe - I've even tested it... just in case it was my error that
introduced your with/end with problem


HTH,
Gareth

-----------------------
Option Explicit

Sub CommandBarLoad()

Dim NewItem As CommandBarControl
Dim NewSubItem As CommandBarControl
Dim NewSubSubItem As CommandBarControl
Const TOOLBAR_NAME As String = "ScottBar"

'Create our commandbar
On Error GoTo Errorhandler_ToolbarExists
Application.CommandBars.Add(Name:=TOOLBAR_NAME, Position:=msoBarTop,
Temporary:=True).Visible = True
On Error GoTo 0

With Application.CommandBars(TOOLBAR_NAME)

'Here we just add a button with an icon
Set NewItem = .Controls.Add(Type:=msoControlButton)
With NewItem
.Caption = "&Open Something"
.OnAction = "OpenSomething"
.Style = msoButtonIcon
.BeginGroup = True
.FaceId = 620
End With

'This is a button with a text label
Set NewItem = .Controls.Add(Type:=msoControlButton)
With NewItem
.Caption = "&Close Something"
.OnAction = "CloseSomething"
.Style = msoButtonCaption
.BeginGroup = True
.TooltipText = "Write something here if you like"
End With

'Now let's have a button with sub buttons thus:
'MY TOOLS - Do Good
' Do Better
' Do Best
' - COLOUR - Make Red
' - Make Blue
' - Make Green
' - Toggle Flag
'
Set NewItem = .Controls.Add(Type:=msoControlPopup)
With NewItem
.Caption = "My T&ools"
.BeginGroup = True

Set NewSubItem = .Controls.Add(Type:=msoControlButton)
With NewSubItem
.Caption = "Do &Good"
.OnAction = "DoGood"
.Style = msoButtonCaption
End With

Set NewSubItem = .Controls.Add(Type:=msoControlButton)
With NewSubItem
.Caption = "Do &Better"
.OnAction = "DoBetter"
.Style = msoButtonCaption
End With

Set NewSubItem = .Controls.Add(Type:=msoControlButton)
With NewSubItem
.Caption = "Do B&est"
.OnAction = "DoBest"
.Style = msoButtonCaption
End With

Set NewSubItem = .Controls.Add(Type:=msoControlPopup)
With NewSubItem
.Caption = "&Colour"

Set NewSubSubItem = .Controls.Add(Type:=msoControlButton)
With NewSubSubItem
.Caption = "Make &Red"
.OnAction = "MakeRed"
.Style = msoButtonCaption
End With

Set NewSubSubItem = .Controls.Add(Type:=msoControlButton)
With NewSubSubItem
.Caption = "Make &Blue"
.OnAction = "MakeBlue"
.Style = msoButtonCaption
End With

Set NewSubSubItem = .Controls.Add(Type:=msoControlButton)
With NewSubSubItem
.Caption = "Make &Green"
.OnAction = "MakeGreen"
.Style = msoButtonCaption
End With

End With

Set NewSubItem = .Controls.Add(Type:=msoControlButton)
With NewSubItem
.Caption = "&Toggle Flag"
.OnAction = "fcnToggleFlag"
.Style = msoButtonCaption
End With

End With

End With

'tidy up
Set NewItem = Nothing
Set NewSubItem = Nothing
Set NewSubSubItem = Nothing

'Skip ErrorHandling Section
Exit Sub

Errorhandler_ToolbarExists:
'Let's delete it and rebuild....
Application.CommandBars(TOOLBAR_NAME).Delete
Resume

End Sub

Function fcnToggleFlag() As Boolean
Const TOOLBAR_NAME As String = "ScottBar"
On Error GoTo ErrorHandler
With Application.CommandBars(TOOLBAR_NAME).Controls("My
Tools").Controls("Toggle Flag")
.State = Not .State
End With

Exit Function
ErrorHandler:
CommandBarLoad
Resume
End Function

That's fabulous, thanks very much Gareth. I'll have a good look at your code
and get it all working from there I'm sure. Your help and time have been
greatly appreciated.

"Gareth" wrote:

Hi Scott,

Stick with it. It's just because you're not familiar with it. I find CBs
far easier - because I'm used to them... And you can do as many submenus
as you need.

Below I've pasted code that demonstrates a submenu and a submenu of a
submenu as well as a working toggling, checkmark. And - would you
believe - I've even tested it... just in case it was my error that
introduced your with/end with problem


HTH,
Gareth

-----------------------
Option Explicit

Sub CommandBarLoad()

Dim NewItem As CommandBarControl
Dim NewSubItem As CommandBarControl
Dim NewSubSubItem As CommandBarControl
Const TOOLBAR_NAME As String = "ScottBar"

'Create our commandbar
On Error GoTo Errorhandler_ToolbarExists
Application.CommandBars.Add(Name:=TOOLBAR_NAME, Position:=msoBarTop,
Temporary:=True).Visible = True
On Error GoTo 0

With Application.CommandBars(TOOLBAR_NAME)

'Here we just add a button with an icon
Set NewItem = .Controls.Add(Type:=msoControlButton)
With NewItem
.Caption = "&Open Something"
.OnAction = "OpenSomething"
.Style = msoButtonIcon
.BeginGroup = True
.FaceId = 620
End With

'This is a button with a text label
Set NewItem = .Controls.Add(Type:=msoControlButton)
With NewItem
.Caption = "&Close Something"
.OnAction = "CloseSomething"
.Style = msoButtonCaption
.BeginGroup = True
.TooltipText = "Write something here if you like"
End With

'Now let's have a button with sub buttons thus:
'MY TOOLS - Do Good
' Do Better
' Do Best
' - COLOUR - Make Red
' - Make Blue
' - Make Green
' - Toggle Flag
'
Set NewItem = .Controls.Add(Type:=msoControlPopup)
With NewItem
.Caption = "My T&ools"
.BeginGroup = True

Set NewSubItem = .Controls.Add(Type:=msoControlButton)
With NewSubItem
.Caption = "Do &Good"
.OnAction = "DoGood"
.Style = msoButtonCaption
End With

Set NewSubItem = .Controls.Add(Type:=msoControlButton)
With NewSubItem
.Caption = "Do &Better"
.OnAction = "DoBetter"
.Style = msoButtonCaption
End With

Set NewSubItem = .Controls.Add(Type:=msoControlButton)
With NewSubItem
.Caption = "Do B&est"
.OnAction = "DoBest"
.Style = msoButtonCaption
End With

Set NewSubItem = .Controls.Add(Type:=msoControlPopup)
With NewSubItem
.Caption = "&Colour"

Set NewSubSubItem = .Controls.Add(Type:=msoControlButton)
With NewSubSubItem
.Caption = "Make &Red"
.OnAction = "MakeRed"
.Style = msoButtonCaption
End With

Set NewSubSubItem = .Controls.Add(Type:=msoControlButton)
With NewSubSubItem
.Caption = "Make &Blue"
.OnAction = "MakeBlue"
.Style = msoButtonCaption
End With

Set NewSubSubItem = .Controls.Add(Type:=msoControlButton)
With NewSubSubItem
.Caption = "Make &Green"
.OnAction = "MakeGreen"
.Style = msoButtonCaption
End With

End With

Set NewSubItem = .Controls.Add(Type:=msoControlButton)
With NewSubItem
.Caption = "&Toggle Flag"
.OnAction = "fcnToggleFlag"
.Style = msoButtonCaption
End With

End With

End With

'tidy up
Set NewItem = Nothing
Set NewSubItem = Nothing
Set NewSubSubItem = Nothing

'Skip ErrorHandling Section
Exit Sub

Errorhandler_ToolbarExists:
'Let's delete it and rebuild....
Application.CommandBars(TOOLBAR_NAME).Delete
Resume

End Sub

Function fcnToggleFlag() As Boolean
Const TOOLBAR_NAME As String = "ScottBar"
On Error GoTo ErrorHandler
With Application.CommandBars(TOOLBAR_NAME).Controls("My
Tools").Controls("Toggle Flag")
.State = Not .State
End With

Exit Function
ErrorHandler:
CommandBarLoad
Resume
End Function