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.

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.

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.

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.