Hello

I've created an excel database in Excel 2007. Depending on the responses on
the first sheet, different responses appear on the next sheet.

The difficulty lies in the second sheet where it's possible to have more
than 1 response per cell. Conversely. tf there is no response in the first
sheet for a particular cell, then it's left blank in the second sheet.

I would like to get the row height for the second sheet to adjust based on
possible responses. Is this possible?

I'm thinking of a macro that would do the trick but I'm not sure of how it
works. While searching for possible answers in this community, I found a
macro created by Gord Dibben. I got this VBA error and wasn't sure of how to
proceed.

Any advice would be greatly appreciated.

Well, we are not likely to know the macro that you mention, so please
post it here.

Pete

On Sep 8, 3:08*pm, forest8 wrote:
Hello

I've created an excel database in Excel 2007. Depending on the responses on
the first sheet, different responses appear on the next sheet.

The difficulty lies in the second sheet where it's possible to have more
than 1 response per cell. Conversely. tf there is no response in the first
sheet for a particular cell, then it's left blank in the second sheet.

I would like to get the row height for the second sheet to adjust based on
possible responses. *Is this possible?

I'm thinking of a macro that would do the trick but I'm not sure of how it
works. While searching for possible answers in this community, I found a
macro created by Gord Dibben. I got this VBA error and wasn't sure of how to
proceed.

Any advice would be greatly appreciated.

The first one was

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


The second one was:

In place of the word "password" you would hard code your password and remove
the leading apostrophe assuming it's password protected. Otherwise ignore it.
Minimal testing:-

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
Dim ProtectStatus As Boolean

With Target
If .MergeCells And .WrapText Then
ProtectStatus = Me.ProtectContents
If ProtectStatus Then Me.Unprotect ' "password"
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
On Error Resume Next
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
On Error GoTo 0
Application.ScreenUpdating = True
If ProtectStatus Then Me.Protect ' "password"
End If
End With
End Sub

"Pete_UK" wrote:

Well, we are not likely to know the macro that you mention, so please
post it here.

Pete

On Sep 8, 3:08 pm, forest8 wrote:
Hello

I've created an excel database in Excel 2007. Depending on the responses on
the first sheet, different responses appear on the next sheet.

The difficulty lies in the second sheet where it's possible to have more
than 1 response per cell. Conversely. tf there is no response in the first
sheet for a particular cell, then it's left blank in the second sheet.

I would like to get the row height for the second sheet to adjust based on
possible responses. Is this possible?

I'm thinking of a macro that would do the trick but I'm not sure of how it
works. While searching for possible answers in this community, I found a
macro created by Gord Dibben. I got this VBA error and wasn't sure of how to
proceed.

Any advice would be greatly appreciated.

Do you have code that provides the "more than one response in a cell" on
sheet2?

If so, post it. We may be able to revise to wrap text and auofit the row of
that cell.


Gord Dibben MS Excel MVP

On Tue, 8 Sep 2009 07:08:03 -0700, forest8
wrote:

Hello

I've created an excel database in Excel 2007. Depending on the responses on
the first sheet, different responses appear on the next sheet.

The difficulty lies in the second sheet where it's possible to have more
than 1 response per cell. Conversely. tf there is no response in the first
sheet for a particular cell, then it's left blank in the second sheet.

I would like to get the row height for the second sheet to adjust based on
possible responses. Is this possible?

I'm thinking of a macro that would do the trick but I'm not sure of how it
works. While searching for possible answers in this community, I found a
macro created by Gord Dibben. I got this VBA error and wasn't sure of how to
proceed.

Any advice would be greatly appreciated.

It's a bit complicated.

Let's just say that in the first sheet, if the response in B1 = yes, then in
sheet 2, the response for B1 would be Condition A, Condition B (I've used
alt-enter to separate the two responses) else is "Blank" (two spaces).

If the response in B2 = yes in sheet one, then the response in sheet 2 for
B2 would be Condition B, Condition C, else is "Blank" (two spaces).

Etc.

"Gord Dibben" wrote:

Do you have code that provides the "more than one response in a cell" on
sheet2?

If so, post it. We may be able to revise to wrap text and auofit the row of
that cell.


Gord Dibben MS Excel MVP

On Tue, 8 Sep 2009 07:08:03 -0700, forest8
wrote:

Hello

I've created an excel database in Excel 2007. Depending on the responses on
the first sheet, different responses appear on the next sheet.

The difficulty lies in the second sheet where it's possible to have more
than 1 response per cell. Conversely. tf there is no response in the first
sheet for a particular cell, then it's left blank in the second sheet.

I would like to get the row height for the second sheet to adjust based on
possible responses. Is this possible?

I'm thinking of a macro that would do the trick but I'm not sure of how it
works. While searching for possible answers in this community, I found a
macro created by Gord Dibben. I got this VBA error and wasn't sure of how to
proceed.

Any advice would be greatly appreciated.

Something like this?

=IF(Sheet1!B1="yes","conditiona" & CHAR(10) & "conditionb","")

With wrap and autofit enabled?

The code you posted deals with merged cells.

Do you have any merged cells?


Gord Dibben MS Excel MVP


On Tue, 8 Sep 2009 09:21:01 -0700, forest8
wrote:

It's a bit complicated.

Let's just say that in the first sheet, if the response in B1 = yes, then in
sheet 2, the response for B1 would be Condition A, Condition B (I've used
alt-enter to separate the two responses) else is "Blank" (two spaces).

If the response in B2 = yes in sheet one, then the response in sheet 2 for
B2 would be Condition B, Condition C, else is "Blank" (two spaces).

Etc.

"Gord Dibben" wrote:

Do you have code that provides the "more than one response in a cell" on
sheet2?

If so, post it. We may be able to revise to wrap text and auofit the row of
that cell.


Gord Dibben MS Excel MVP

On Tue, 8 Sep 2009 07:08:03 -0700, forest8
wrote:

Hello

I've created an excel database in Excel 2007. Depending on the responses on
the first sheet, different responses appear on the next sheet.

The difficulty lies in the second sheet where it's possible to have more
than 1 response per cell. Conversely. tf there is no response in the first
sheet for a particular cell, then it's left blank in the second sheet.

I would like to get the row height for the second sheet to adjust based on
possible responses. Is this possible?

I'm thinking of a macro that would do the trick but I'm not sure of how it
works. While searching for possible answers in this community, I found a
macro created by Gord Dibben. I got this VBA error and wasn't sure of how to
proceed.

Any advice would be greatly appreciated.

Hi there

There isn't any merged cells in my file. Also, I have wrap text enabled but
how do I enable autofit?

Thanks

"Gord Dibben" wrote:

Something like this?

=IF(Sheet1!B1="yes","conditiona" & CHAR(10) & "conditionb","")

With wrap and autofit enabled?

The code you posted deals with merged cells.

Do you have any merged cells?


Gord Dibben MS Excel MVP


On Tue, 8 Sep 2009 09:21:01 -0700, forest8
wrote:

It's a bit complicated.

Let's just say that in the first sheet, if the response in B1 = yes, then in
sheet 2, the response for B1 would be Condition A, Condition B (I've used
alt-enter to separate the two responses) else is "Blank" (two spaces).

If the response in B2 = yes in sheet one, then the response in sheet 2 for
B2 would be Condition B, Condition C, else is "Blank" (two spaces).

Etc.

"Gord Dibben" wrote:

Do you have code that provides the "more than one response in a cell" on
sheet2?

If so, post it. We may be able to revise to wrap text and auofit the row of
that cell.


Gord Dibben MS Excel MVP

On Tue, 8 Sep 2009 07:08:03 -0700, forest8
wrote:

Hello

I've created an excel database in Excel 2007. Depending on the responses on
the first sheet, different responses appear on the next sheet.

The difficulty lies in the second sheet where it's possible to have more
than 1 response per cell. Conversely. tf there is no response in the first
sheet for a particular cell, then it's left blank in the second sheet.

I would like to get the row height for the second sheet to adjust based on
possible responses. Is this possible?

I'm thinking of a macro that would do the trick but I'm not sure of how it
works. While searching for possible answers in this community, I found a
macro created by Gord Dibben. I got this VBA error and wasn't sure of how to
proceed.

Any advice would be greatly appreciated.

FormatRowAutofit


Gord

On Tue, 8 Sep 2009 17:27:01 -0700, forest8
wrote:

Hi there

There isn't any merged cells in my file. Also, I have wrap text enabled but
how do I enable autofit?

Thanks

"Gord Dibben" wrote:

Something like this?

=IF(Sheet1!B1="yes","conditiona" & CHAR(10) & "conditionb","")

With wrap and autofit enabled?

The code you posted deals with merged cells.

Do you have any merged cells?


Gord Dibben MS Excel MVP


On Tue, 8 Sep 2009 09:21:01 -0700, forest8
wrote:

It's a bit complicated.

Let's just say that in the first sheet, if the response in B1 = yes, then in
sheet 2, the response for B1 would be Condition A, Condition B (I've used
alt-enter to separate the two responses) else is "Blank" (two spaces).

If the response in B2 = yes in sheet one, then the response in sheet 2 for
B2 would be Condition B, Condition C, else is "Blank" (two spaces).

Etc.

"Gord Dibben" wrote:

Do you have code that provides the "more than one response in a cell" on
sheet2?

If so, post it. We may be able to revise to wrap text and auofit the row of
that cell.


Gord Dibben MS Excel MVP

On Tue, 8 Sep 2009 07:08:03 -0700, forest8
wrote:

Hello

I've created an excel database in Excel 2007. Depending on the responses on
the first sheet, different responses appear on the next sheet.

The difficulty lies in the second sheet where it's possible to have more
than 1 response per cell. Conversely. tf there is no response in the first
sheet for a particular cell, then it's left blank in the second sheet.

I would like to get the row height for the second sheet to adjust based on
possible responses. Is this possible?

I'm thinking of a macro that would do the trick but I'm not sure of how it
works. While searching for possible answers in this community, I found a
macro created by Gord Dibben. I got this VBA error and wasn't sure of how to
proceed.

Any advice would be greatly appreciated.