Trapping Group/Ungroup Event

I have also posted this question at Mr Excel but thought this may in fact be the more appropriate forum to ask this question.

http://www.mrexcel.com/board2/viewto...6557&highlight=

Hello All,

I have a spreadsheet that contains a number of groupings that are obtained by accessing Data>Group and Outline>Group.

I would like to trap the process of clicking on the + or - sign that groups or ungroups the selection as an event but have no idea how to go about.

Does anyone have any thoughts?

This is a quick macro (Macro1) recorded with a test workbook. And above it is some modifications to bend it to your will MWAHAHAHA :devil: . First you select the range or you can define your own range object and assign a range to it eg. then using the selection object asssign or unassign your groups.

Hope this helps
Andrew ;?

Code:

---

Dim MyRange As Range
Dim StartCell As String, EndCell As String
'just example cells
StartCell = "C3"
EndCell = "C6"
Set MyRange = Range(StartCell + ":" + EndCell)
MyRange.Select
'Then use the selection object
'which ever one it is that you want to group or ungroup.
'and it works for columns too
'To group
Selection.Rows.Group
'Or to ungroup
Selection.Rows.UnGroup

 

Sub Macro1()
'Macro1 Macro
' Macro recorded 18/03/2005 by Sandam
Range("A1:C10").Select
Selection.Rows.Group
Selection.Columns.Group
Selection.Rows.Ungroup
End Sub

---

Sandam,

Thanks for the reply. The event i am actually trying to trap is not the grouping itself but the actual expanding and collapsing after the range has been grouped. I hope that makes sense. Any thoughts for that.

IrishDave1

it does. i'll investigate a little more for you :)

I really appreciate it sandam. My initially feeling is that i will have to create some kind of class module to trap the event. But as to how each of the individual expand and contract buttons would be referenced is beyond me???

Okay. this is what I coul find out. There is a method called ExecuteExcel4Macro with a string parameter and this has something to do with the groupings and the showing of details. the only problem is I can seem to get it to work outside of a recorded macro?? very strange. Perhaps someone more knowledgeable in Excel will be able to figure it out, personally I'm stumped.


'This works on R1C1 calls??

Code:

---

ExecuteExcel4Macro "SHOW.DETAIL(2,3,TRUE,,0)"

---

Sandam, you are an absolute legend. I think i can work with that. The reason it doesn't seem to be working is that an extra comma has been inserted after the True. Remove that comma and it works. It appears to me that it is indeed using R1C1 referencing. I get the true and false part as well but what the last number signifies is still abit of a mystery. Thank you so much for the help. I at least have something i can play with even though it is still not quite actually trapping the event. Really appreciate the help.


EDIT: Figured it out, the last number is signifying the number of columns that are having their detail shown.

IrishDave1

Excellent. Glad i could at least point you in the right direction.

Andrew;?

P.s Don't forget to mark your thread solved using the thread tools :D

Always wondered how that seemed to happen so consistently. Thanks again.

IrishDave1