Center Toolbar

[Zone]

When my workbook opens, I make all commandbars invisible except the
standard menubar and the workbook's custom toolbar. I have the custom
toolbar in the right place vertically (just below the menubar). How
do I center it horizontally? TIA, James

 

[Zone]

When my workbook opens, I make all commandbars invisible except the
standard menubar and the workbook's custom toolbar.  I have the custom
toolbar in the right place vertically (just below the menubar).  How
do I center it horizontally?  TIA, James

I mean, I want VBA to center my toolbar. J

 

[Helmut Weber]

Hi James,

When my workbook opens, I make all commandbars invisible except the
standard menubar and the workbook's custom toolbar. I have the custom
toolbar in the right place vertically (just below the menubar). How
do I center it horizontally? TIA, James

for centering a toolbar:

Sub TestCenter()
Dim x As Long
Dim v As Long
v = Application.CommandBars("formatting").Width
x = System.HorizontalResolution
Application.CommandBars("formatting").Left = x / 2 - v / 2
End Sub

Though occasionally,
System.HorizontalResolution tells me,
that an ActiveX object could not be created.
Why? I don't know.
--
Greetings from Bavaria, Germany

Helmut Weber, MVP WordVBA

Win XP, Office 2003
"red.sys" & Chr$(64) & "t-online.de"

 

[Zone]

Helmut, line
x = System.HorizontalResolution
gives me run-time error 424, object required. I am using Excel 2002
on Windows XP. James

 

[Helmut Weber]

Hi James,

there must be a bug in it.
I got the same message a few times,
then it disappeared.

I never get it in Word.

If the macro is just for you,
you may try it with 1024 or 1200 or whatever.

And it applies to a maximized window.

--
Greetings from Bavaria, Germany

Helmut Weber, MVP WordVBA

Win XP, Office 2003
"red.sys" & Chr$(64) & "t-online.de"

 

[Zone]

Danka, Helmut, and hallo to beautiful Bavaria. I'm using a wide-
screen laptop, if that makes any difference. I will try your
suggestion. J

 

[Zone]

Helmut, this works fine:
Sub TestCenterJ()
Dim WideScr As Variant
Dim x As Long
Dim v As Long
v = Application.CommandBars("formatting").Width
WideScr = MsgBox("Wide screen?", vbYesNo)
If WideScr = vbYes Then
x = 1325
Else
x = 1024
End If
Application.CommandBars("formatting").Left = x / 2 - v / 2
End Sub
Of course, the problem is the program should be able to automatically
gather this screen horiz center information. Since your
System.HorizontalResolution won't work for me (Excel 2002), I'm at a
loss. Having to ask the user for this info is really embarrassing!
James

 

[Helmut Weber]

Hi Zone,

if for professional use:

Option Explicit
Declare Function GetSystemMetrics Lib "user32" _
(ByVal nIndex As Long) As Long

Sub Test()
MsgBox GetSystemMetrics(16)
End Sub

HTH

--
Greetings from Bavaria, Germany

Helmut Weber, MVP WordVBA

Win XP, Office 2003
"red.sys" & Chr$(64) & "t-online.de"

 

[Zone]

Helmut, The msgbox displays 1280 on my wide-screen laptop. Would you
happen to know whether this is pixels or points? J

 

[Darren Hill]

That will be pixels.
Your screen must have a resolution of 1280x800.

Darren

 

[Zone]

Is there a way to convert pixels into points? Many thanks, James

 

[Helmut Weber]

Hi James,

there is more than one bug in this in Excel:

Sub x()
' MsgBox PixelsToPoints(1280) ' error error error
' works perfectly in Word
' or
MsgBox 1280 / 96 * 72 '!
End Sub

--
Greetings from Bavaria, Germany

Helmut Weber, MVP WordVBA

Win XP, Office 2003
"red.sys" & Chr$(64) & "t-online.de"

 

[Zone]

Guess it's time for me to figure out another strategy. Thanks for the
info.
James

 

[Jim Cone]

James,
Centering under the menu bar on my system looks about right...
Sub MakeItHappen()
Dim lngPlace As Long
lngPlace = Application.CommandBars(1).Width / 2
Application.CommandBars("MyCustomBar").Left = _
lngPlace - (Application.CommandBars("MyCustomBar").Width / 2)
End Sub
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware


Guess it's time for me to figure out another strategy.
Thanks for the info.
James

 

[Zone]

Thanks, Jim. Looks pretty good. It is a bit too far to the right on
the widescreen laptop. Who knew just centering a toolbar would be so
difficult? I'll probably go with this solution since at least the
toolbar will always land in the same place, and, in addition to
centering it, I want to be able to show userforms approximately
centered over the icon that calls them. Regards, James