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() |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Screen position of commandbar button in Excel 2007 ?
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. |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Screen position of commandbar button in Excel 2007 ?
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" wrote in message ... 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. |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Screen position of commandbar button in Excel 2007 ?
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!!! |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Screen position of commandbar button in Excel 2007 ?
"minimaster" wrote in message 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 |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Screen position of commandbar button in Excel 2007 ?
See the popup example on this page
http://www.rondebruin.nl/qat.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "minimaster" wrote in message ... 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. |
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 |