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

Do you have under ToolsProtectionProtect SheetAllow 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


On Wed, 9 Jul 2008 15:29:02 -0700, Kathleen
wrote:

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

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" wrote:

Do you have under ToolsProtectionProtect SheetAllow 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


On Wed, 9 Jul 2008 15:29:02 -0700, Kathleen
wrote:

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

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

On Mon, 14 Jul 2008 08:41:02 -0700, Kathleen
wrote:

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" wrote:

Do you have under ToolsProtectionProtect SheetAllow 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


On Wed, 9 Jul 2008 15:29:02 -0700, Kathleen
wrote:

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

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" wrote:

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

On Mon, 14 Jul 2008 08:41:02 -0700, Kathleen
wrote:

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" wrote:

Do you have under ToolsProtectionProtect SheetAllow 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


On Wed, 9 Jul 2008 15:29:02 -0700, Kathleen
wrote:

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

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

On Tue, 15 Jul 2008 08:48:02 -0700, Kathleen
wrote:

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" wrote:

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

On Mon, 14 Jul 2008 08:41:02 -0700, Kathleen
wrote:

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" wrote:

Do you have under ToolsProtectionProtect SheetAllow 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


On Wed, 9 Jul 2008 15:29:02 -0700, Kathleen
wrote:

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