Screen position of commandbar button in Excel 2007 ?

[minimaster]

I have some add-ins with custom commandbars that have buttons on them
that show a popup menu below the commandbar button when clicked . That
worked fine so far up to Excel 2003.
However in Excel 2007
Application.CommandBars.ActionControl.Top
and
Application.CommandBars.ActionControl.Left
does not deliver anymore the information about the screen position of
the clicked button, so as a consequence my popup menu does get
displayed at the wrong position in Excel 2007. Is there any
workaround, maybe with an Windows API call to get the position of the
commandbar button? I'm explicity interested in the position of the
commandbar button, not the position of the mouse click, because aI
have another button on the commandbar simulates a spinbutton control,
and for that I need to determine whether the mouse click was in the
upper or lower half of the "spin button" on the commandbar.

 

[Peter T]

For me .ActionControl.Left/Top fail in both 2003 & 2007 with the clicked
button button in a popup bar. However
..ActionControl.Parent.Left/Top seems OK. So it means you'll need to work out
the offset of your known button from the top/left of the bar and size,
should be relatively straightforward. Maybe something like this (only
lightly tested) -

Private Declare Function GetAsyncKeyState Lib "user32.dll" ( _
ByVal vKey As Long) As Integer

Private Declare Function GetCursorPos Lib "user32.dll" ( _
ByRef lpPoint As POINTAPI) As Long
Private Type POINTAPI
x As Long
y As Long
End Type


Function GetRelVertical() As Currency
Dim idx As Long
Dim pCur As POINTAPI
Dim pBtnSize As POINTAPI, pBtnTL As POINTAPI
Dim RelHoriz As Currency, RelVert As Currency

If GetAsyncKeyState(vbKeyReturn) <> 0 Then
GetRelVertical = 12345 ' Enter pressed
Exit Function
End If

GetCursorPos pCur

With Application.CommandBars.ActionControl
idx = .Index
pBtnSize.x = .Width
pBtnSize.y = .Height

pBtnTL.x = .Parent.Left + 2
pBtnTL.y = .Parent.Top + 2 + (idx - 1) * (pBtnSize.y + 1)
End With

RelHoriz = (pCur.x - pBtnTL.x) / pBtnSize.x
RelVert = (pCur.y - pBtnTL.y) / pBtnSize.y

GetRelVertical = RelVert ' should be a decimal 0-1

' Debug.Print RelHoriz, RelVert

End Function


Return GetRelVertical as the first line in the macro called from the popup.

Might need to experient a bit but this seemed pretty accurate for me in both
2003/2007

Not sure if you want the relative horizontal or vertical clicked position,
both in the demo.

I assume if user selects a popup button with the keyboard and presses Enter
you need to know about it (just added that in at the last moment)

Regards,
Peter T

 

[minimaster]

My commandbar button isn't a popup in itself, its just a "simple"
commandbar button which calls a procedure that then dynamically
creates a fully independent popup menu. The problem is to have the
newly created popup menu show up right below the commandbar button in
order to give the illusion that the button behaves like a popup menu.
This approach worked fine in 2003 and was choosen because there is no
such thing like a "popup button" in the excel 2003 object model.
Because I have a simple commandbar button I don't need to reference
the parent. However the .top information isn't provided correctly in
2007. And I'm afraid your code snippets don't do it either in 2007.
Plus there is no such thing like a vertical arrangement for a
commandbar in 2007. All commanddbars are dispalyed horizontally
aligned under the Add-ins tab - or I'm missing something?

 

[minimaster]

What I don' fully understand is that with a normal window position,
let's say the excel window is maximized; I get for
Application.CommandBars.ActionControl.Top
and for
Application.CommandBars.ActionControl.Parent.Top
the value of -4

This value moves up and down when I move the excel window in its
position but it seems it hasn't much to do with the position of the
commandbar button.
Is this a side effect of a castrated command bar object model in Excel
2007?

 

[Peter T]

In your OP you said you have a popup menu which I took to mean a popup
commandbar (btw controls are shown vertically on those) and that's what the
example was aimed at. Ie, call the function from a button on a popup
commandbar. For me it worked fine in both 2003 and 2007, actually I thought
rather well!

If it's not a popup, explain what you mean, I don't follow what you describe
below (if not a popup). Better still post the code to create whatever it is.

Note, although popups work in Excel 2007 normal commandbars don't.

Regards,
Peter T

 

[minimaster]

I'm sorry that my explanation was too complicated, let me try to
explain it more simple plus some code for illustration purposes:

On a commandbar I have a button - snippet from the creation of this
button:

Dim hgCmdBar As CommandBar
Dim combut As CommandBarButton

Set hgCmdBar = Application.CommandBars.Add(Name:="myPivot_Tools")
With hgCmdBar.Controls
Set combut = .Add(msoControlButton)
With combut
.Style = msoButtonIcon
.FaceID=59
.TooltipText = "Show my special custom popup menu
for PivotTable tools"
.OnAction = ShowCustomPopup
.Visible = True
.Enabled = True
End With
End With

in the procedure

Sub ShowCustomPopup()
Dim buttonTop as Long

buttonTop=Application.CommandBars.ActionControl.Top
...
End Sub

I would like to know at which screen position I can find the button
which I have on the above mentioned commandbar.
In Excel 2003 this was as simple as shown above in the code snippet
from Sub ShowCustomPopup()

 

[Peter T]

So there's no popup at all, only a routine that's partly named "Popup"
called by a normal button on a normal bar - right?

As I mentioned before, in Excel 2007 custom commandbars do not exist (other
than popups), at least not in the way you intend to use them. Instead the
equivalent controls are added to the Ribbon in the Add-Ins tab.

I don't know how you'd get the position of a Ribbon control, the only
properties it appears to expose in a Call-back (control As IRibbonControl)
are Context, Id & Tag; nothing about location. Whilst not impossible I
suspect it'd be extremely difficult to work out its location.

I didn't get the original objective, something about simulating a
spinbutton - maybe time to put your own controls on the Ribbon, start here -

http://www.rondebruin.nl/ribbon.htm

Also look at Andy Pope's superb Ribbon Editor
http://www.andypope.info/vba/ribboneditor.htm

Regards,
Peter T

 

[minimaster]

Right - in the context of the core problem. I want show a popup menu
underneath a button when the button is clicked. And therefore I'm
after the postion of such button. I'll live with the crumbled
commandbar if that can't be fixed somehow. Converting all my
commandbars into the ribbon style is not an option and not critical
just because of the problem with the button position. Still it would
be nice to solve this.

 

[Peter T]

Maybe there are different approaches, eg adapt to a userform (captionless
perhaps) or a "real" popup as I had in mind! Or two buttons in the Ribbon,
up & down.

Regards,
Peter T

 

[minimaster]

I'm aware of those options. Just apain in the .. and very time
consuming to convert existing commandbars when you have quite a few of
those with certain special functions.
My No.1 wish for Office 2010: full backward compatibility for
commandbars as they existed in 2003.
My number of clicks in the Ribbon interface has gone up considerably
since I lost my customized/optimized 2003 UI. Unfortunately I'm left
with no real option as certain workspace functions from our comapny
does require Excel 2010 to be installed. Frustrating!!!

 

[Peter T]

I'm aware of those options. Just apain in the .. and very time
consuming to convert existing commandbars when you have quite a few of
those with certain special functions.

Shouldn't take so long, presumably the main code will stay the same, just
the UI, unless you need to update loads of workbooks.

My No.1 wish for Office 2010: full backward compatibility for
commandbars as they existed in 2003.

It's not going to happen!

My number of clicks in the Ribbon interface has gone up considerably
since I lost my customized/optimized 2003 UI. Unfortunately I'm left
with no real option as certain workspace functions from our comapny
does require Excel 2010 to be installed. Frustrating!!!

Regards,
Peter T

 

[Ron de Bruin]

See the popup example on this page
http://www.rondebruin.nl/qat.htm