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 2003 and Command Bars
- Steve Vincent
- Site Admin
- Posts: 1054
- 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
If this were a dictatorship, it would be a heck of a lot easier, just so long as I'm the dictator.
Production: Planning Analytics 64 bit 2.0.5, Windows 2016 Server. Excel 2016, IE11 for t'internet
Production: Planning Analytics 64 bit 2.0.5, Windows 2016 Server. Excel 2016, IE11 for t'internet
- Alan Kirk
- Site Admin
- Posts: 6606
- Joined: Sun May 11, 2008 2:30 am
- OLAP Product: TM1
- Version: PA2.0.9.18 Classic NO PAW!
- Excel Version: 2013 and Office 365
- Location: Sydney, Australia
- Contact:
Re: Excel 2003 and Command Bars
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.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?
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.
-----------
Before posting, please check the documentation, the FAQ, the Search function and FOR THE LOVE OF GLUB the Request Guidelines.
- Steve Vincent
- Site Admin
- Posts: 1054
- 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
Oops, yeah i probably could have made the fact that i was creating the bars with VBA a little more obvious
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
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: Planning Analytics 64 bit 2.0.5, Windows 2016 Server. Excel 2016, IE11 for t'internet
Production: Planning Analytics 64 bit 2.0.5, Windows 2016 Server. Excel 2016, IE11 for t'internet