Herbie has a Calendar spreadsheet being populated by a huge concatination formula. He asks if there is a way to combine Wrap Cells and Shrink to Fit as he works in the calendar. In Episode #1472 Bill shows us one way to accomplish this.
...This blog is the video podcast companion to the book, Learn Excel 2007 through Excel 2010 from MrExcel. Download a new two minute video every workday to learn one of the 512 Excel Mysteries Solved! and 35% More Tips than the previous edition of Bill's book!
"The Learn Excel from MrExcel Podcast Series"
MrExcel.com — Your One Stop for Excel Tips and Solutions. Visit us today!
...This blog is the video podcast companion to the book, Learn Excel 2007 through Excel 2010 from MrExcel. Download a new two minute video every workday to learn one of the 512 Excel Mysteries Solved! and 35% More Tips than the previous edition of Bill's book!
"The Learn Excel from MrExcel Podcast Series"
MrExcel.com — Your One Stop for Excel Tips and Solutions. Visit us today!
Transcript of the video:
MrExcel podcast is sponsored by Easy-XL.
Learn Excel from MrExcel podcast, episode 1472: wrap cells and shrink to fit.
Hey, welcome back to MrExcel netcast.
I'm Bill Jelen.
Today's question is sent in by Herbie.
Herbie has a calendar and the calendar is being populated by these huge concatenation formulas.
The height and width of each cell is fixed; you can't change it.
He wants them to be consistent throughout, but he would like to turn on both wrap text and shrink to fit.
Unfortunately, that is not something we do.
We do control one.
You see, the shrink to fit is available, but as soon as your turn on wrap text, it is grayed out.
He says, “What I would like is, you know, the length of the entries is anywhere from 3 to 120 characters and I'd like the text to get smaller as it gets longer,” but he says, “When it's just three, I don't want it to be 72 point.
I don't want it to be, you know, completely insane, you know.
Is there some way to do that?” Well, yes, we can do that with VBA.
So, I’m going to switch over here, Alt F11, and I’m going to find--there is podcast 1472, go to the sheet that has the calendar and double-click that sheet.
Now, this is a special place for event handler macros.
So, we're going to choose worksheet from the left and then from the right, we're going to choose calculate.
So every time one of those formulas calculates this little bit of code-- I’m just going to run.
I've already written the code.
I’m going to paste it in.
We will talk about what it's doing here, let’s get rid of the extra-- Okay, so, I've already hardcoded that the calendar is in B4 to H11.
You can't see H11, but trust me it goes down to that.
We're going to look at each cell in that range and then use the LEN function, the length function, to figure out how many characters are in the result of that formula at this point in time.
If it's less than 30, I’m just going to leave it at 11, but as we get longer and longer and longer and longer, I keep making the font size smaller.
All right, so, let's come back here and we will calculate this.
First of all, let’s see if it's-- if it's working.
All right, so, here I can see that it's not quite right.
It's not quite working in the way that I want it to work.
So, maybe we need to make the size smaller.
We’ll kind of go 9 7 5 4 or something like that and you'll really just kind of have to do-- do some testing.
There we go.
So, now, it's all fitting in that cell and as we add more descriptions to the places where this is pulling from, then it automatically gets smaller.
Now, it requires some tweaking.
It's not as good as just turning on wrap text, but if you think about it, you know, in 15 minutes of testing this text and seeing how it looks at that size, you should come up with something pretty clever.
So, that’s automatically going to 7 point and this one here, which is shorter, stays at eleven point.
Again, is that underlying cells down here change?
Let's just come down here and we'll type something really, really, really long.
We go back up.
Hopefully, that cell would have-- yes, it automatically got smaller in response to the fact that the worksheet calculated, which happened in response to the fact that I changed one of those underlying cells.
So, kind of cool little away-- although that's probably too small, but again, some tweaks here and Herbie would be good to go.
Combining wrap text with a different VBA shrink to fit.
Great question.
I want to thank Herbie for sending that question in and I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
Learn Excel from MrExcel podcast, episode 1472: wrap cells and shrink to fit.
Hey, welcome back to MrExcel netcast.
I'm Bill Jelen.
Today's question is sent in by Herbie.
Herbie has a calendar and the calendar is being populated by these huge concatenation formulas.
The height and width of each cell is fixed; you can't change it.
He wants them to be consistent throughout, but he would like to turn on both wrap text and shrink to fit.
Unfortunately, that is not something we do.
We do control one.
You see, the shrink to fit is available, but as soon as your turn on wrap text, it is grayed out.
He says, “What I would like is, you know, the length of the entries is anywhere from 3 to 120 characters and I'd like the text to get smaller as it gets longer,” but he says, “When it's just three, I don't want it to be 72 point.
I don't want it to be, you know, completely insane, you know.
Is there some way to do that?” Well, yes, we can do that with VBA.
So, I’m going to switch over here, Alt F11, and I’m going to find--there is podcast 1472, go to the sheet that has the calendar and double-click that sheet.
Now, this is a special place for event handler macros.
So, we're going to choose worksheet from the left and then from the right, we're going to choose calculate.
So every time one of those formulas calculates this little bit of code-- I’m just going to run.
I've already written the code.
I’m going to paste it in.
We will talk about what it's doing here, let’s get rid of the extra-- Okay, so, I've already hardcoded that the calendar is in B4 to H11.
You can't see H11, but trust me it goes down to that.
We're going to look at each cell in that range and then use the LEN function, the length function, to figure out how many characters are in the result of that formula at this point in time.
If it's less than 30, I’m just going to leave it at 11, but as we get longer and longer and longer and longer, I keep making the font size smaller.
All right, so, let's come back here and we will calculate this.
First of all, let’s see if it's-- if it's working.
All right, so, here I can see that it's not quite right.
It's not quite working in the way that I want it to work.
So, maybe we need to make the size smaller.
We’ll kind of go 9 7 5 4 or something like that and you'll really just kind of have to do-- do some testing.
There we go.
So, now, it's all fitting in that cell and as we add more descriptions to the places where this is pulling from, then it automatically gets smaller.
Now, it requires some tweaking.
It's not as good as just turning on wrap text, but if you think about it, you know, in 15 minutes of testing this text and seeing how it looks at that size, you should come up with something pretty clever.
So, that’s automatically going to 7 point and this one here, which is shorter, stays at eleven point.
Again, is that underlying cells down here change?
Let's just come down here and we'll type something really, really, really long.
We go back up.
Hopefully, that cell would have-- yes, it automatically got smaller in response to the fact that the worksheet calculated, which happened in response to the fact that I changed one of those underlying cells.
So, kind of cool little away-- although that's probably too small, but again, some tweaks here and Herbie would be good to go.
Combining wrap text with a different VBA shrink to fit.
Great question.
I want to thank Herbie for sending that question in and I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
