De-selecting without collapsing?

[horsefish01]

As seen in this sheet.

I need the info to stay, I just want it to be not-visible - and collapsing is not a possibilty because I need to see keep the data in all other columns visible.

So person 10 gets fired/quits, I deselect him in sheet 1(it can collaps if needed) - but in sheet2 & 3 I need person 10 to blacken or something that makes that person not visible in the tabel!

Sheet.xlsx

[Richard Buttrey]

Hi,

Does the attached help.

It uses an old Excel4 Get.Cell macro to determine the height of rows in sheet1. This detail is recorded in the helper column M on sheet2.
The Sheet1 Deactivate event is used to refresh the Sheet2 row height column M formulae.

A Conditional format uses the row height to determine whether to make the font colour white.

[horsefish01]

Hi,

Does the attached help.

It uses an old Excel4 Get.Cell macro to determine the height of rows in sheet1. This detail is recorded in the helper column M on sheet2.
The Sheet1 Deactivate event is used to refresh the Sheet2 row height column M formulae.

A Conditional format uses the row height to determine whether to make the font colour white.

That looks great.

But I somehow can't find the macro-code itself(noob), so I cant figure out how to edit it and its conditions!?

[Richard Buttrey]

Hi,

The Excel3 Get.Cell is actually a range name called (from memory) RowHeight.

If you look in the Formulas...Name Manager you should easily spot it. These Get... 'macros' can't be directly used in cells hence the need to use a named formula.

[horsefish01]

Okay, I think I follow so far - the thing is when I open "VBA", there is no code present at all. So I cant re-configure the macro itself

[Kaper]

VBA code is only (as Richard said) in Ark1 code and it is event handler for Deactivate.
To change/reconfigure main "working piece", open Name Manager (Ctrl+F3) and edit definition of named range Rowheight.

[horsefish01]

Thanks


I'm so noobish at this,

Here

Please Login or Register  to view this content.

what does the 17 stand for?

[Kaper]

Glad to hear :-)

So if (as I assume), that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

[horsefish01]

Well I thought I had it. Guess I didnt.

Heres one I made, I must be missing something completly!

Testen.xlsm

[horsefish01]

God danmit, finally got it :-P

[Richard Buttrey]

Well done for persevering. These old Get.Cell formulae are not particularly intuitive, but they're a lot more powerful that the more 'up to date ) CELL() function.

Thanks for the rep.