Automatically resizing rows when value changed

[john_london]

Hi All

So, at the moment I have two date cells where users can change a month or year. Depending on what they select, some text - using a formula - is shown within a merged cell. I want the cell to automatically update in height to fit the text, and I'm nearly there.

So far I have this code in the worksheet where the cell is:

Please Login or Register  to view this content.

I also have some more code in the same sheet:

Please Login or Register  to view this content.

Now, the code works, but only if I select another sheet and then go back. What I essentially need to do is combine the two so the user doesn't have to select a different sheet!

Any ideas?

Thanks,
John

[Jim885]

Wouldn't this work?

Please Login or Register  to view this content.

Maybe

Please Login or Register  to view this content.

Or are there any merged cells in the rows? Merged cells create problems. Use Center Across Selection instead.

[john_london]

Thanks for the reply. Yep - the cell is merged. I think it improves formatting and makes it look nicer... and it seems like I'm almost there with the merged cells anyway.

[john_london]

Anyone?

Thanks...

[Jim885]

You can use CENTER ACROSS SECTION and achieve the same "looks" without the problems of merged cells. Merged cells and VBA don't get along.

[john_london]

Thanks again, I've tried using center across selection, but the text is centered.

[HaHoBe]

Hi, john_london,

if you monitor a cell which has a formula inserted you would need to apply the Calculate-event as that would be triggered on any calculation/change of formula. Workshet_change would need to monitor both cells which might be changed.

You might try to alter the code like

Please Login or Register  to view this content.

Ciao,
Holger

[john_london]

Hi Holger - thanks so much for replying. This is the code I used, but the row height stays the same? It seems it only updates when I have the calculate code you mention separately in the WorkSheet_Activate sub?

Please Login or Register  to view this content.

So, when I have the original code that I posted it works (the row height adjusts) when I select a different sheet and then come back to the original worksheet. I would hope that the user can just update the cell and the row updates rather than having to switch sheets.

Please Login or Register  to view this content.

[john_london]

Could I maybe send you the Excel spreadsheet?

[john_london]

Okay - I think it's best if I attach the spreadsheet and explain what's going on.

So,if you open the spreadsheet and change the month from 'September' to 'August', you will see the comments are shorter but the height of the cell stays the same. If you then click on the 'Data' tab, then go back to the 'Summary' tab, the height of the cell correctly adjusts. What I want to get is this happening without having to click on the 'Data' tab and back to the 'Summary' tab!

[HaHoBe]

Hi, john_london,

change

Please Login or Register  to view this content.

which will not be triggered as the cell monitored is changed by formula not by entry/edit/query to

Please Login or Register  to view this content.

which will monitor the cells changing to start the event.

Ciao,
Holger

[john_london]

Hi Holger. Thank you so much! This is perfect.

Really appreciate your time and effort.

Regards,
John

[HaHoBe]

Hi, John,

if your problem has been resolved please mark this thread as Solved via Thread Tools over the first post in this thread.

Ciao,
Holger