Tom,
I'm having problem centering my userforms horizontally by using
me.left=application.commandbars("mytb").controls(6 ).left
and so forth. Any suggestions? James

I just noticed you wanted to center it "just below" the tool bar button
instead of over top of it. This would be preferable I'm sure. The logic that
does the centering is quite simplistic and I'm sure you can adjust the code
to suit. The code should accomodate different screen resolutions.

You might also consider going with a popup tool bar like the one when you
right click over the worksheet.

Regards,
Greg

"Zone" wrote:

Tom,
I'm having problem centering my userforms horizontally by using
me.left=application.commandbars("mytb").controls(6 ).left
and so forth. Any suggestions? James

Step 1:-

In the UF code module insert the following. The part that positions the
top-left corner of the UF at the center of the button and the MsgBox code is
illustrative only. Delete after testing it.

Private Sub UserForm_Activate()
Me.Top = BCnt.Top
Me.Left = BCnt.Left
MsgBox "Userform top corner centered on button"
Me.Top = BCnt.Top - Me.Height / 2
Me.Left = BCnt.Left - Me.Width / 2
MsgBox "Userform center centered on button"
End Sub

Step 2:-

In a standard module insert the following code.

Option Explicit
Public Type BtnCenter
Left As Single
Top As Single
End Type
Public BCnt As BtnCenter

Private Declare Function GetDeviceCaps Lib "Gdi32" _
(ByVal hDC As Long, ByVal nIndex As Long) As Long
Private Declare Function GetDC Lib "user32" _
(ByVal hwnd As Long) As Long
Private Declare Function ReleaseDC Lib "user32" _
(ByVal hwnd As Long, ByVal hDC As Long) As Long
Private Const LOGPIXELSX = 88
Private Const LOGPIXELSY = 90

Sub CenterUF()
With Application.CommandBars.ActionControl
BCnt.Left = (.Left + .Width / 2) * PPPX
BCnt.Top = (.Top + .Height / 2) * PPPY
End With
UserForm1.Show
End Sub

Private Function PPPX() As Double
Dim hDC As Long
hDC = GetDC(0)
PPPX = 72 / GetDeviceCaps(hDC, LOGPIXELSX)
ReleaseDC 0, hDC
End Function

Private Function PPPY() As Double
Dim hDC As Long
hDC = GetDC(0)
PPPY = 72 / GetDeviceCaps(hDC, LOGPIXELSY)
ReleaseDC 0, hDC
End Function

Step 3:-

Assing the "CenterUF" macro to your button on the custom tool bar.

Regards,
Greg


"Zone" wrote:

Tom,
I'm having problem centering my userforms horizontally by using
me.left=application.commandbars("mytb").controls(6 ).left
and so forth. Any suggestions? James