Excel: group (expand/collapse rows) event trigger (to unhide a colum)

[tankthefrank]

Hi folks,
Hope all is well!

I simply want to add in some vba to unhide column D whenever "any" of the group row expand buttons on the worksheet are selected.
Then re-hide Col D when "any" collapse button is selected.

It is prob a simple one for you wizards but it would really help me. Cheers

 

[SkipVought]

Hi,

There is no EVENT associated with group expand/collapse.

What is the business case for this requirement? Maybe there's another way to satisfy the basic requirement.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[tankthefrank]

thanks for the quick response.
I have a column which only has values in collapsed rows.
So I would prefer to hide the column until required.
In terms of bus requirement, well the boss is one of those "less is more" types and intimated that i expand rows/column by double clicking on a cell value. I don't want to do this cos i think it would require hard coding the cell/ranges etc in the worksheet code.

 

[SkipVought]

only has values in collapsed rows.

Hmmmmmm. Belt and suspenders. WHY?

Why even mess with trying to "enhance" native Excel features? Geez, there must be so many other fish to fry, but maybe your boss has time & money to throw away with such foolishness. Seems to me that you already have all the functionality that your requirements need!

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[tankthefrank]

haha. Myself and the boss are not excel experts so it is more of a learning point for me really. If it could be done great, if it can't then fine. So there is DEFINITELY no way of expanding rows and cols at the same time?

 

[SkipVought]

Check out in VBA Help Using Events with Microsoft Excel Objects. It will give you an idea what kinds of things can be done using events in Excel.

Have you had any VBA coding experience? Its not the kind of thing you can casually pick up over the weekend. But, on the other hand, its not rocket science, either.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[tankthefrank]

i have seen plenty of vba over the last 5 yrs but never got my hands dirty to be honest. Ill check it out, cheers