Cell resizing

[Kathleen]

Hi,

I have a protected worksheet that looks like an evaluation form with rating
scale, etc. In various areas, there are sections that the user needs to text
in comments. The cell is formatted for wrapping but it does not adjust the
size of the cell in order to accomodate multiple sentences. Is it possible?

Kathleen

 

[Gord Dibben]

Do you have under Tools>Protection>Protect Sheet>Allow users to:

Format rows and Format Cells checkmarked?

Do you have the appropriate Rows set to Autofit?

Note: if any merged cells, these will not autofit to accommodate wrapped text.


Gord Dibben MS Excel MVP

 

[Kathleen]

Hi Gord,

Thank you for your response. I thought it might be because of the merged
cells. Couldn't get the form to work in Word and it's a a 10 page document
in Excel, so there are many merged cells in order to print and use the
document as well as enter in it online. Back to square one for me.

Kathleen

 

[Gord Dibben]

You can use event code to autofit the merged cells if you choose to use them.

Here is code from Greg Wilson.

Make sure cells are set to wrap text and rows set to autofit.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim NewRwHt As Single
Dim cWdth As Single, MrgeWdth As Single
Dim c As Range, cc As Range
Dim ma As Range

With Target
If .MergeCells And .WrapText Then
Set c = Target.Cells(1, 1)
cWdth = c.ColumnWidth
Set ma = c.MergeArea
For Each cc In ma.Cells
MrgeWdth = MrgeWdth + cc.ColumnWidth
Next
Application.ScreenUpdating = False
ma.MergeCells = False
c.ColumnWidth = MrgeWdth
c.EntireRow.AutoFit
NewRwHt = c.RowHeight
c.ColumnWidth = cWdth
ma.MergeCells = True
ma.RowHeight = NewRwHt
cWdth = 0: MrgeWdth = 0
Application.ScreenUpdating = True
End If
End With
End Sub


Gord

 

[Kathleen]

I'm sorry to say I'm a total novice with VBA and haven't a clue how to set up
the event code properly. I opened VBA, procedure, and copied/pasted your
language but am not sure which or what needs to be adjusted to fit my
template. I'm going through my course book now to see if it will help me to
understand better. Its a huge relief to know that there is a work around to
make this document function the way we hope. I can't thank you enough for
helping me with this issue!

 

[Gord Dibben]

Greg's code is worksheet event code.

Right-click on the worksheet tab and "View Code".

Copy/paste into that sheet module.

Alt + q to return to the Excel window.

Make sure cells are set for wrap text and rows to autofit.


Gord

 

[Kathleen]

Hi Gord,

I followed your instructions. I'm sorry to say it didn't work. Made sure
all cells are set to wrap. On the rows, it was kind of odd - highlighted
entire document, went to format, rows and autofit. It appeared to have
worked through the document, took a few minutes. But it doesn't show as
active or checked or anything...is a toggle key or am I doing it wrong?
Also, the document as list boxes, and is password protected, could that be
stopping the autofit?

Whatever the case may be, I am very grateful for your help!

Kathleen

 

[Kathleen]

I was testing a few ideas and think the problem may be that I have a lot of
merged cells. When I insert a new row, it automatically adjusts to the
extended text and won't in the areas where two or more cells have been
merged. Is there any work around for this one?

 

[Gord Dibben]

If the rows have been formatted to Autofit you will not see any indication other
than the rows will expand to fit.

I prpbably would have formatted only a few rows to test but you've done it.

On the protection issue.........................

You should get a 1004 error message when the code tries to run on a protected
sheet..........unless when you set protection you checkmarked allow users to
"Format Rows" the rows should expand with no error

If you don't want to set that...................

You can work around it by unprotect and re-protect in the code.

Revised version.......................

Private Sub Worksheet_Change(ByVal Target As Range)
Dim NewRwHt As Single
Dim cWdth As Single, MrgeWdth As Single
Dim c As Range, cc As Range
Dim ma As Range
On Error GoTo endit
Application.EnableEvents = False
With Target
If .MergeCells And .WrapText Then
ActiveSheet.Unprotect Password:="justme"
Set c = Target.Cells(1, 1)
cWdth = c.ColumnWidth
Set ma = c.MergeArea
For Each cc In ma.Cells
MrgeWdth = MrgeWdth + cc.ColumnWidth
Next
Application.ScreenUpdating = False
ma.MergeCells = False
c.ColumnWidth = MrgeWdth
c.EntireRow.AutoFit
NewRwHt = c.RowHeight
c.ColumnWidth = cWdth
ma.MergeCells = True
ma.RowHeight = NewRwHt
cWdth = 0: MrgeWdth = 0
Application.ScreenUpdating = True
End If
End With
endit:
Application.EnableEvents = True
ActiveSheet.Protect Password:="justme"
End Sub


Gord

 

[Gord Dibben]

Whoops!

Should have read this post before replying to the other<g>

The newly inserted row will autofit if it has no merged cells so that is normal.

When you say "two or more cells have been merged" what do you mean?

That's what merged cells are.

Give an example of some of the cells you have merged

i.e. A1 and A2 merged into A1 or G2 and G3 merged into G2

I personnally never use merged cells due to the myriad of problems they create,
one of which is autofitting.


Gord

 

[Kathleen]

Hi Gord,

Yes, the merged cells are becoming very unfriendly. Problem is that the
document really should have been created in word since it has so much text.
Excel was the only other option. There are merged cells like you said - A1 -
C1, etc but there are also merged cells like A1 - A3. Some have list boxes
for the use to choose from so that they only pick one of the numbers listed.
I tried to see if I created a new line, merging A1 - F1 and then using the
text box but it still wouldn't cooperate. --can't move off sheet.

Kathleen

 

[Spiky]

Why do you need them Merged? Often the results can be duplicated with
other means, like Center Across, which will not impact your column
formatting. And careful formatting of borders and whatnot.

 

[Kathleen]

Hi Spiky,

I used Center across for some areas (gives the appearance of merged cells?)
and then some areas can't be centered since it disrupts the look of the
document. There are list boxes for the user to choose an option and then
formulas based on those choices are calculated automatically (keeps them from
bad math issues ).

Document is heavy on text, followed by free form sections, and list boxes.
It goes across 16 columns (A - P).

By the way, I have to tell you that our in-house "experts" are clueless and
the amount of help/suggestions I've received from all of you has been
amazing. It really makes us wonder why we are paying for our so called
expert trainers.

Kathleen