Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Screen position of commandbar button in Excel 2007 ?
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Screen position of commandbar button in Excel 2007 ?
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" wrote in message ... 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Screen position of commandbar button in Excel 2007 ?
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? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Screen position of commandbar button in Excel 2007 ?
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? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Screen position of commandbar button in Excel 2007 ?
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" wrote in message ... 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? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Screen position of commandbar button in Excel 2007 ?
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.To p ... 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() |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Screen position of commandbar button in Excel 2007 ?
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" wrote in message ... 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.To p ... 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() |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Command button position and screen resolution | Excel Programming | |||
Force CommandBar Position | Excel Programming | |||
Position of CellCursor on Screen (absolute position) | Excel Programming | |||
Get floating CommandBar position | Excel Programming | |||
Adjust position of a commandbar | Excel Programming |