Screen position of commandbar button in Excel 2007 ?
minimaster
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
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" <hartmut.g...@bakerhughes.com> wrote in message
news:04b15a4d-d802-4062...@m16g2000yqc.googlegroups.com...
minimaster
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
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
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" <hartmut.g...@bakerhughes.com> wrote in message
news:e62549a8-3564-4594...@m25g2000yqc.googlegroups.com...
minimaster
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
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" <hartmut.g...@bakerhughes.com> wrote in message
news:34f44346-51b6-497f...@p8g2000yqb.googlegroups.com...
minimaster
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
perhaps) or a "real" popup as I had in mind! Or two buttons in the Ribbon,
up & down.
Regards,
Peter T
"minimaster" <hartmut.g...@bakerhughes.com> wrote in message
news:252e79f6-4100-42ae...@m25g2000yqc.googlegroups.com...
minimaster
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
> 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
http://www.rondebruin.nl/qat.htm
--
Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm
"minimaster" <hartmut.g...@bakerhughes.com> wrote in message
news:252e79f6-4100-42ae...@m25g2000yqc.googlegroups.com...