Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Imitating Pivot table's context awareness
When you click inside of a pivot table, it's area lights up with a blue
border and the pivot table menu pops up. How does it know this? Is it a control (like an embedded option button or combobox), or ... i'm not sure what else there is. Regardless, suppose I want the same thing in one of my XL apps. I would like to have the spreadsheet know when I enter a special region, changing the context menus, showing/hiding custom floating controlbars, etc. However, I would like it to be general enough such that it is not tied to any particular workbook (meaning, I assume, it must be in an XLA or COM addin). And although I have an idea how to implement this, I would like to know if anyone else has tried this, and if so, what problems they encountered. I assume that I would be able to do all of the contextual stuff just by keeping a log of what the special regions are and trapping the selection change event, but I have absolutely no idea to implement something like the PivotTable's ability to display the text "Drop Data Items Here" on top of the spreadsheet when active or handle problems like the user inserting rows above the special region, thereby changing its address, or the the pivot table denying the user the ability to delete rows inside of it ("You canno change, move a part of, or insert cells in a PivotTable report..."). Any help would be greatly appreciated. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Imitating Pivot table's context awareness
I would suspect your special features would be limited to what you can write
in VBA and what is available to you in the object model. locking the cells, protecting the sheet and setting enableselection to xlUnlockedCells would do a reasonable approximation to one aspect that you described (as an example). But you basic statement of instantiating application events and using the change event is seems reasonable. Of course if you want to subclass excel and hook into the windows message que and so forth, that would give you greater control. -- regards, Tom Ogilvy "R Avery" wrote in message ... When you click inside of a pivot table, it's area lights up with a blue border and the pivot table menu pops up. How does it know this? Is it a control (like an embedded option button or combobox), or ... i'm not sure what else there is. Regardless, suppose I want the same thing in one of my XL apps. I would like to have the spreadsheet know when I enter a special region, changing the context menus, showing/hiding custom floating controlbars, etc. However, I would like it to be general enough such that it is not tied to any particular workbook (meaning, I assume, it must be in an XLA or COM addin). And although I have an idea how to implement this, I would like to know if anyone else has tried this, and if so, what problems they encountered. I assume that I would be able to do all of the contextual stuff just by keeping a log of what the special regions are and trapping the selection change event, but I have absolutely no idea to implement something like the PivotTable's ability to display the text "Drop Data Items Here" on top of the spreadsheet when active or handle problems like the user inserting rows above the special region, thereby changing its address, or the the pivot table denying the user the ability to delete rows inside of it ("You canno change, move a part of, or insert cells in a PivotTable report..."). Any help would be greatly appreciated. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Imitating Pivot table's context awareness
Thanks.
By the way, how would i Sub-class Excel? Is this possible to do with VB? Tom Ogilvy wrote: I would suspect your special features would be limited to what you can write in VBA and what is available to you in the object model. locking the cells, protecting the sheet and setting enableselection to xlUnlockedCells would do a reasonable approximation to one aspect that you described (as an example). But you basic statement of instantiating application events and using the change event is seems reasonable. Of course if you want to subclass excel and hook into the windows message que and so forth, that would give you greater control. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Imitating Pivot table's context awareness
Not my area of Expertise, but there is an Oreilly book "subclassing and
hooking with VB", so I have not doubt it is possible. http://www.amazon.com/exec/obidos/tg...glance&s=books -- Regards, Tom Ogilvy "R Avery" wrote in message ... Thanks. By the way, how would i Sub-class Excel? Is this possible to do with VB? Tom Ogilvy wrote: I would suspect your special features would be limited to what you can write in VBA and what is available to you in the object model. locking the cells, protecting the sheet and setting enableselection to xlUnlockedCells would do a reasonable approximation to one aspect that you described (as an example). But you basic statement of instantiating application events and using the change event is seems reasonable. Of course if you want to subclass excel and hook into the windows message que and so forth, that would give you greater control. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
PIVOT TABLE's Custom Calculated Fields | Excel Discussion (Misc queries) | |||
how do I extend an existing pivot table's range | Excel Discussion (Misc queries) | |||
Changing a pivot table's data range in Excel 2007 | Excel Worksheet Functions | |||
Accessing Pivot Table's Data in VBA | Excel Programming | |||
Accessing Pivot Table's Data | Excel Programming |