Excel 2003 and Command Bars

Not related to a specific OLAP tool. (Includes forum policies and rules).
Post Reply
User avatar
Steve Vincent
Site Admin
Posts: 1048
Joined: Mon May 12, 2008 8:33 am
OLAP Product: TM1
Version: 10.2.2 FP1
Excel Version: 2010
Location: UK

Excel 2003 and Command Bars

Post by Steve Vincent » Wed Mar 04, 2009 10:16 am

This has been an annoyance for ages but i've never found a solution as yet. When you create a floating menu bar you can control where it's placed on the screen, but if you use top/left/bottom/right it "stacks" the new bar next to the others even if there is loads of space on the bar. If you have addins that only take up a small amount of screen space, it drives me mad because it takes far too much screen space unnecessarily.

Anyone know how to control the location within the bar, so that it automatically looks like the 2nd screen rather than the 1st when it's loaded?
excel menu before.gif
excel menu before.gif (23.46 KiB) Viewed 1224 times
excel menu after.gif
excel menu after.gif (18.58 KiB) Viewed 1225 times
If this were a dictatorship, it would be a heck of a lot easier, just so long as I'm the dictator.
Production: TM1 64 bit 10.2.2, Windows 2008/2012 Server. Excel 2010, IE11 for t'internet

User avatar
Alan Kirk
Site Admin
Posts: 5724
Joined: Sun May 11, 2008 2:30 am
OLAP Product: TM1
Version: 9.5.2 64 bit moving to 10.2.2
Excel Version: 2010
Location: Sydney, Australia
Contact:

Re: Excel 2003 and Command Bars

Post by Alan Kirk » Wed Mar 04, 2009 10:18 pm

Steve Vincent wrote:This has been an annoyance for ages but i've never found a solution as yet. When you create a floating menu bar you can control where it's placed on the screen, but if you use top/left/bottom/right it "stacks" the new bar next to the others even if there is loads of space on the bar. If you have addins that only take up a small amount of screen space, it drives me mad because it takes far too much screen space unnecessarily.

Anyone know how to control the location within the bar, so that it automatically looks like the 2nd screen rather than the 1st when it's loaded?
At first I was in a state of puzzlement about that post. Then it dawned on me that you were talking about adding the toolbar via VBA, not the GUI.

I believe that the following code provides what you need. It will add a new toolbar to the right of the Standard one, if the Standard one is there. If there's already a further toolbar to the right of that, that other toolbar will just be shoved further to the right.

The key to it is assigning the .Left property and the .RowIndex property to the new toolbar, the latter of which MUST be assigned directly from the property of the toolbar that you're putting it to the right of, NOT via a variable.

Obviously you can play around with this and loop through the toolbars to determine what's where and set your toolbar location accordingly. This is left as an exercise for the reader.

Code: Select all

Sub CreateToolbar()

Dim s_Msg As String

Dim cbrNew As CommandBar
Dim cbrStandard As CommandBar
Dim l_cbrStandardWidth As Long
Dim l_cbrStandardLeft As Long

On Error GoTo ErrorHandler

'Just in case it already exists.
On Error Resume Next
Application.CommandBars("TestPos").Delete

Set cbrStandard = Application.CommandBars("Standard")
On Error GoTo ErrorHandler

If cbrStandard Is Nothing Then
    Err.Raise vbObjectError + 1000, , "No Standard toolbar."
End If

l_cbrStandardWidth = cbrStandard.Width
l_cbrStandardLeft = cbrStandard.Left

Set cbrNew = CommandBars.Add(Name:="TestPos")

With cbrNew
    .Position = msoBarTop
    .Left = l_cbrStandardLeft + l_cbrStandardWidth
    
    'Don't try assigning the .RowIndex from the
    'other toolbar to a variable and then
    'assigning it here; that will merely bump the other toolbar
    'out of the way. Always assign directly from the
    'other bar's property.
    .RowIndex = cbrStandard.RowIndex
    
    .Visible = True
End With

'Do the code for adding the buttons
'to the bar yourself, you lazy sod.

ExitPoint:

Set cbrNew = Nothing
Set cbrStandard = Nothing

Exit Sub

ErrorHandler:
s_Msg = "D'OH! Error " & Err.Number & vbCrLf _
 & Err.Description

MsgBox s_Msg, vbCritical

Resume ExitPoint

End Sub
"To them, equipment failure is terrifying. To me, it’s 'Tuesday.' "
-----------
Before posting, please check the documentation, the FAQ, the Search function and FOR THE LOVE OF GLUB the Request Guidelines.

User avatar
Steve Vincent
Site Admin
Posts: 1048
Joined: Mon May 12, 2008 8:33 am
OLAP Product: TM1
Version: 10.2.2 FP1
Excel Version: 2010
Location: UK

Re: Excel 2003 and Command Bars

Post by Steve Vincent » Thu Mar 05, 2009 9:58 am

Oops, yeah i probably could have made the fact that i was creating the bars with VBA a little more obvious :oops:

Thanks for that Alan, makes perfect sense now. The biggest issue was the row property, understanding that has removed my annoyance and now i have a toolbar that does what it should rather than take up half the screen, cheers :)
If this were a dictatorship, it would be a heck of a lot easier, just so long as I'm the dictator.
Production: TM1 64 bit 10.2.2, Windows 2008/2012 Server. Excel 2010, IE11 for t'internet

Post Reply