+ Reply to Thread
Results 1 to 13 of 13

newbie VBA help please

  1. #1
    SteveFerd
    Guest

    newbie VBA help please

    Hello, Thanks in advance for your help.

    Before I even ask my question, I am far from being a programer and don't
    really even know enough to be dangerous. I am developing a template for a
    group of 40 sales reps which will be used to track large accounts. The
    template requires the use of merged cells which will need to be able to use
    word wrap. I followed previous advice and use the following code from Jim
    Rech:
    ''Simulates row height autofit for a merged cell if the active cell..
    '' is merged.
    '' has Wrap Text set.
    '' includes only 1 row.
    ''Unlike real autosizing the macro only increases row height
    '' (if needed). It does not reduce row height because another
    '' merged cell on the same row may needed a greater height
    '' than the active cell.
    Sub AutoFitMergedCellRowHeight()
    Dim CurrentRowHeight As Single, MergedCellRgWidth As Single
    Dim CurrCell As Range
    Dim ActiveCellWidth As Single, PossNewRowHeight As Single
    If ActiveCell.MergeCells Then
    With ActiveCell.MergeArea
    If .Rows.Count = 1 And .WrapText = True Then
    Application.ScreenUpdating = False
    CurrentRowHeight = .RowHeight
    ActiveCellWidth = ActiveCell.ColumnWidth
    For Each CurrCell In Selection
    MergedCellRgWidth = CurrCell.ColumnWidth + MergedCellRgWidth
    Next
    .MergeCells = False
    .Cells(1).ColumnWidth = MergedCellRgWidth
    .EntireRow.AutoFit
    PossNewRowHeight = .RowHeight
    .Cells(1).ColumnWidth = ActiveCellWidth
    .MergeCells = True
    .RowHeight = IIf(CurrentRowHeight > PossNewRowHeight, _
    CurrentRowHeight, PossNewRowHeight)
    End If
    End With
    End If
    End Sub

    This works fine, just need it to be automatic. So I added this to the
    worksheet:

    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    AutoFitMergedCellRowHeight
    End Sub

    This mostly works. When I enter text in a merged cell which will require
    wrap then press enter, it does not automatically wrap. But when I go back
    and click on the cell, it wraps. What do I need to do so I don't have to go
    back and click on the cell?

    Thanks,
    Steve




  2. #2
    Jim Thomlinson
    Guest

    RE: newbie VBA help please

    Don't use selection change. Try the change event...

    Private Sub Worksheet_Change(ByVal Target As Range)
    AutoFitMergedCellRowHeight
    End Sub
    --
    HTH...

    Jim Thomlinson


    "SteveFerd" wrote:

    > Hello, Thanks in advance for your help.
    >
    > Before I even ask my question, I am far from being a programer and don't
    > really even know enough to be dangerous. I am developing a template for a
    > group of 40 sales reps which will be used to track large accounts. The
    > template requires the use of merged cells which will need to be able to use
    > word wrap. I followed previous advice and use the following code from Jim
    > Rech:
    > ''Simulates row height autofit for a merged cell if the active cell..
    > '' is merged.
    > '' has Wrap Text set.
    > '' includes only 1 row.
    > ''Unlike real autosizing the macro only increases row height
    > '' (if needed). It does not reduce row height because another
    > '' merged cell on the same row may needed a greater height
    > '' than the active cell.
    > Sub AutoFitMergedCellRowHeight()
    > Dim CurrentRowHeight As Single, MergedCellRgWidth As Single
    > Dim CurrCell As Range
    > Dim ActiveCellWidth As Single, PossNewRowHeight As Single
    > If ActiveCell.MergeCells Then
    > With ActiveCell.MergeArea
    > If .Rows.Count = 1 And .WrapText = True Then
    > Application.ScreenUpdating = False
    > CurrentRowHeight = .RowHeight
    > ActiveCellWidth = ActiveCell.ColumnWidth
    > For Each CurrCell In Selection
    > MergedCellRgWidth = CurrCell.ColumnWidth + MergedCellRgWidth
    > Next
    > .MergeCells = False
    > .Cells(1).ColumnWidth = MergedCellRgWidth
    > .EntireRow.AutoFit
    > PossNewRowHeight = .RowHeight
    > .Cells(1).ColumnWidth = ActiveCellWidth
    > .MergeCells = True
    > .RowHeight = IIf(CurrentRowHeight > PossNewRowHeight, _
    > CurrentRowHeight, PossNewRowHeight)
    > End If
    > End With
    > End If
    > End Sub
    >
    > This works fine, just need it to be automatic. So I added this to the
    > worksheet:
    >
    > Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    > AutoFitMergedCellRowHeight
    > End Sub
    >
    > This mostly works. When I enter text in a merged cell which will require
    > wrap then press enter, it does not automatically wrap. But when I go back
    > and click on the cell, it wraps. What do I need to do so I don't have to go
    > back and click on the cell?
    >
    > Thanks,
    > Steve
    >
    >
    >


  3. #3
    STEVE BELL
    Guest

    Re: newbie VBA help please

    If you want it to happen when you edit a cell,
    use an event macro - place it in the worksheet module.

    Private Sub Worksheet_Change(ByVal Target As Range)
    AutoFitMergedCellRowHeight
    End Sub

    You can make the code more selective using if statements
    target.address="$A$1"
    target.row = 1
    target.column =1
    or use the intersect function

    write back if you need more help...
    --
    steveB

    Remove "AYN" from email to respond
    "SteveFerd" <[email protected]> wrote in message
    news:[email protected]...
    > Hello, Thanks in advance for your help.
    >
    > Before I even ask my question, I am far from being a programer and don't
    > really even know enough to be dangerous. I am developing a template for a
    > group of 40 sales reps which will be used to track large accounts. The
    > template requires the use of merged cells which will need to be able to
    > use
    > word wrap. I followed previous advice and use the following code from Jim
    > Rech:
    > ''Simulates row height autofit for a merged cell if the active cell..
    > '' is merged.
    > '' has Wrap Text set.
    > '' includes only 1 row.
    > ''Unlike real autosizing the macro only increases row height
    > '' (if needed). It does not reduce row height because another
    > '' merged cell on the same row may needed a greater height
    > '' than the active cell.
    > Sub AutoFitMergedCellRowHeight()
    > Dim CurrentRowHeight As Single, MergedCellRgWidth As Single
    > Dim CurrCell As Range
    > Dim ActiveCellWidth As Single, PossNewRowHeight As Single
    > If ActiveCell.MergeCells Then
    > With ActiveCell.MergeArea
    > If .Rows.Count = 1 And .WrapText = True Then
    > Application.ScreenUpdating = False
    > CurrentRowHeight = .RowHeight
    > ActiveCellWidth = ActiveCell.ColumnWidth
    > For Each CurrCell In Selection
    > MergedCellRgWidth = CurrCell.ColumnWidth +
    > MergedCellRgWidth
    > Next
    > .MergeCells = False
    > .Cells(1).ColumnWidth = MergedCellRgWidth
    > .EntireRow.AutoFit
    > PossNewRowHeight = .RowHeight
    > .Cells(1).ColumnWidth = ActiveCellWidth
    > .MergeCells = True
    > .RowHeight = IIf(CurrentRowHeight > PossNewRowHeight, _
    > CurrentRowHeight, PossNewRowHeight)
    > End If
    > End With
    > End If
    > End Sub
    >
    > This works fine, just need it to be automatic. So I added this to the
    > worksheet:
    >
    > Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    > AutoFitMergedCellRowHeight
    > End Sub
    >
    > This mostly works. When I enter text in a merged cell which will require
    > wrap then press enter, it does not automatically wrap. But when I go back
    > and click on the cell, it wraps. What do I need to do so I don't have to
    > go
    > back and click on the cell?
    >
    > Thanks,
    > Steve
    >
    >
    >




  4. #4
    Bob Phillips
    Guest

    Re: newbie VBA help please

    Words of warning.

    First, if you are playing with VBA, you DO know enough to be dangerous <vbg>

    Second, if you can, avoid merged cells. They cause more problems than they
    are worth. You can usually design around it.

    --
    HTH

    Bob Phillips

    "SteveFerd" <[email protected]> wrote in message
    news:[email protected]...
    > Hello, Thanks in advance for your help.
    >
    > Before I even ask my question, I am far from being a programer and don't
    > really even know enough to be dangerous. I am developing a template for a
    > group of 40 sales reps which will be used to track large accounts. The
    > template requires the use of merged cells which will need to be able to

    use
    > word wrap. I followed previous advice and use the following code from Jim
    > Rech:
    > ''Simulates row height autofit for a merged cell if the active cell..
    > '' is merged.
    > '' has Wrap Text set.
    > '' includes only 1 row.
    > ''Unlike real autosizing the macro only increases row height
    > '' (if needed). It does not reduce row height because another
    > '' merged cell on the same row may needed a greater height
    > '' than the active cell.
    > Sub AutoFitMergedCellRowHeight()
    > Dim CurrentRowHeight As Single, MergedCellRgWidth As Single
    > Dim CurrCell As Range
    > Dim ActiveCellWidth As Single, PossNewRowHeight As Single
    > If ActiveCell.MergeCells Then
    > With ActiveCell.MergeArea
    > If .Rows.Count = 1 And .WrapText = True Then
    > Application.ScreenUpdating = False
    > CurrentRowHeight = .RowHeight
    > ActiveCellWidth = ActiveCell.ColumnWidth
    > For Each CurrCell In Selection
    > MergedCellRgWidth = CurrCell.ColumnWidth +

    MergedCellRgWidth
    > Next
    > .MergeCells = False
    > .Cells(1).ColumnWidth = MergedCellRgWidth
    > .EntireRow.AutoFit
    > PossNewRowHeight = .RowHeight
    > .Cells(1).ColumnWidth = ActiveCellWidth
    > .MergeCells = True
    > .RowHeight = IIf(CurrentRowHeight > PossNewRowHeight, _
    > CurrentRowHeight, PossNewRowHeight)
    > End If
    > End With
    > End If
    > End Sub
    >
    > This works fine, just need it to be automatic. So I added this to the
    > worksheet:
    >
    > Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    > AutoFitMergedCellRowHeight
    > End Sub
    >
    > This mostly works. When I enter text in a merged cell which will require
    > wrap then press enter, it does not automatically wrap. But when I go back
    > and click on the cell, it wraps. What do I need to do so I don't have to

    go
    > back and click on the cell?
    >
    > Thanks,
    > Steve
    >
    >
    >




  5. #5
    SteveFerd
    Guest

    Re: newbie VBA help please

    Thanks everyone for your help. I changed my worksheet event from a selection
    change to a change event. Now nothing happens, even when I go back and click
    on the cell after entering the text.
    Also a combination of a change event and an if statement targeting column 4
    didn't work.
    Any other ideas?

    "STEVE BELL" wrote:

    > If you want it to happen when you edit a cell,
    > use an event macro - place it in the worksheet module.
    >
    > Private Sub Worksheet_Change(ByVal Target As Range)
    > AutoFitMergedCellRowHeight
    > End Sub
    >
    > You can make the code more selective using if statements
    > target.address="$A$1"
    > target.row = 1
    > target.column =1
    > or use the intersect function
    >
    > write back if you need more help...
    > --
    > steveB
    >
    > Remove "AYN" from email to respond
    > "SteveFerd" <[email protected]> wrote in message
    > news:[email protected]...
    > > Hello, Thanks in advance for your help.
    > >
    > > Before I even ask my question, I am far from being a programer and don't
    > > really even know enough to be dangerous. I am developing a template for a
    > > group of 40 sales reps which will be used to track large accounts. The
    > > template requires the use of merged cells which will need to be able to
    > > use
    > > word wrap. I followed previous advice and use the following code from Jim
    > > Rech:
    > > ''Simulates row height autofit for a merged cell if the active cell..
    > > '' is merged.
    > > '' has Wrap Text set.
    > > '' includes only 1 row.
    > > ''Unlike real autosizing the macro only increases row height
    > > '' (if needed). It does not reduce row height because another
    > > '' merged cell on the same row may needed a greater height
    > > '' than the active cell.
    > > Sub AutoFitMergedCellRowHeight()
    > > Dim CurrentRowHeight As Single, MergedCellRgWidth As Single
    > > Dim CurrCell As Range
    > > Dim ActiveCellWidth As Single, PossNewRowHeight As Single
    > > If ActiveCell.MergeCells Then
    > > With ActiveCell.MergeArea
    > > If .Rows.Count = 1 And .WrapText = True Then
    > > Application.ScreenUpdating = False
    > > CurrentRowHeight = .RowHeight
    > > ActiveCellWidth = ActiveCell.ColumnWidth
    > > For Each CurrCell In Selection
    > > MergedCellRgWidth = CurrCell.ColumnWidth +
    > > MergedCellRgWidth
    > > Next
    > > .MergeCells = False
    > > .Cells(1).ColumnWidth = MergedCellRgWidth
    > > .EntireRow.AutoFit
    > > PossNewRowHeight = .RowHeight
    > > .Cells(1).ColumnWidth = ActiveCellWidth
    > > .MergeCells = True
    > > .RowHeight = IIf(CurrentRowHeight > PossNewRowHeight, _
    > > CurrentRowHeight, PossNewRowHeight)
    > > End If
    > > End With
    > > End If
    > > End Sub
    > >
    > > This works fine, just need it to be automatic. So I added this to the
    > > worksheet:
    > >
    > > Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    > > AutoFitMergedCellRowHeight
    > > End Sub
    > >
    > > This mostly works. When I enter text in a merged cell which will require
    > > wrap then press enter, it does not automatically wrap. But when I go back
    > > and click on the cell, it wraps. What do I need to do so I don't have to
    > > go
    > > back and click on the cell?
    > >
    > > Thanks,
    > > Steve
    > >
    > >
    > >

    >
    >
    >


  6. #6
    Forum Contributor
    Join Date
    06-10-2005
    Location
    Central Jersey
    Posts
    117
    Okay, this works.
    Try this:

    Make sure you keep the

    Private Sub Worksheet_Change(ByVal Target As Range)
    AutoFitMergedCellRowHeight
    End Sub


    That Jim and Steve told you to change.

    and in your Sub AutoFitMergedCellRowHeight () macro right above the first if statement add:

    ActiveCell.Offset(-1, 0).Select

    and here is what you end up with.



    Sub AutoFitMergedCellRowHeight()

    Dim CurrentRowHeight As Single, MergedCellRgWidth As Single
    Dim CurrCell As Range
    Dim ActiveCellWidth As Single, PossNewRowHeight As Single

    ActiveCell.Offset(-1, 0).Select

    If ActiveCell.MergeCells Then
    With ActiveCell.MergeArea
    If .Rows.Count = 1 And .WrapText = True Then
    Application.ScreenUpdating = False
    CurrentRowHeight = .RowHeight
    ActiveCellWidth = ActiveCell.ColumnWidth
    For Each CurrCell In Selection
    MergedCellRgWidth = CurrCell.ColumnWidth + MergedCellRgWidth
    Next
    .MergeCells = False
    .Cells(1).ColumnWidth = MergedCellRgWidth
    .EntireRow.AutoFit
    PossNewRowHeight = .RowHeight
    .Cells(1).ColumnWidth = ActiveCellWidth
    .MergeCells = True
    .RowHeight = IIf(CurrentRowHeight > PossNewRowHeight, _
    CurrentRowHeight, PossNewRowHeight)
    End If
    End With
    End If

    End Sub

    Private Sub Worksheet_Change(ByVal Target As Range)
    AutoFitMergedCellRowHeight
    End Sub

    ---------------------------------------------------

    Now after you enter the text and you press enter it will change it for you automatically and remain to be the cell that you entered the text in.

    Hope this is what you were looking for!
    -Joseph

  7. #7
    STEVE BELL
    Guest

    Re: newbie VBA help please

    Steve,

    Make sure that the event macro is in the sheet module and not in a regular
    module.
    Here is another version (replace Macro6 with the name of your macro)
    Remember that the change event is fired by entering anything into a cell and
    that leaving that cell.
    It does not fire if you select a cell.

    To target column 4 make sure your if statement
    If target.column = 4 then

    Private Sub Worksheet_Change(ByVal Target As Range)
    Call Macro6
    End Sub

    If this doesn't work
    add a Msgbox line to give you a signal
    add a msgbox to the other macro to see if it gets called.

    Private Sub Worksheet_Change(ByVal Target As Range)
    Msgbox "Event happened"
    Call Macro6
    End Sub

    If none of this helps, than show us your code and tell us where it is
    located....
    --
    steveB

    Remove "AYN" from email to respond
    "SteveFerd" <[email protected]> wrote in message
    news:[email protected]...
    > Thanks everyone for your help. I changed my worksheet event from a
    > selection
    > change to a change event. Now nothing happens, even when I go back and
    > click
    > on the cell after entering the text.
    > Also a combination of a change event and an if statement targeting column
    > 4
    > didn't work.
    > Any other ideas?
    >
    > "STEVE BELL" wrote:
    >
    >> If you want it to happen when you edit a cell,
    >> use an event macro - place it in the worksheet module.
    >>
    >> Private Sub Worksheet_Change(ByVal Target As Range)
    >> AutoFitMergedCellRowHeight
    >> End Sub
    >>
    >> You can make the code more selective using if statements
    >> target.address="$A$1"
    >> target.row = 1
    >> target.column =1
    >> or use the intersect function
    >>
    >> write back if you need more help...
    >> --
    >> steveB
    >>
    >> Remove "AYN" from email to respond
    >> "SteveFerd" <[email protected]> wrote in message
    >> news:[email protected]...
    >> > Hello, Thanks in advance for your help.
    >> >
    >> > Before I even ask my question, I am far from being a programer and
    >> > don't
    >> > really even know enough to be dangerous. I am developing a template
    >> > for a
    >> > group of 40 sales reps which will be used to track large accounts. The
    >> > template requires the use of merged cells which will need to be able to
    >> > use
    >> > word wrap. I followed previous advice and use the following code from
    >> > Jim
    >> > Rech:
    >> > ''Simulates row height autofit for a merged cell if the active cell..
    >> > '' is merged.
    >> > '' has Wrap Text set.
    >> > '' includes only 1 row.
    >> > ''Unlike real autosizing the macro only increases row height
    >> > '' (if needed). It does not reduce row height because another
    >> > '' merged cell on the same row may needed a greater height
    >> > '' than the active cell.
    >> > Sub AutoFitMergedCellRowHeight()
    >> > Dim CurrentRowHeight As Single, MergedCellRgWidth As Single
    >> > Dim CurrCell As Range
    >> > Dim ActiveCellWidth As Single, PossNewRowHeight As Single
    >> > If ActiveCell.MergeCells Then
    >> > With ActiveCell.MergeArea
    >> > If .Rows.Count = 1 And .WrapText = True Then
    >> > Application.ScreenUpdating = False
    >> > CurrentRowHeight = .RowHeight
    >> > ActiveCellWidth = ActiveCell.ColumnWidth
    >> > For Each CurrCell In Selection
    >> > MergedCellRgWidth = CurrCell.ColumnWidth +
    >> > MergedCellRgWidth
    >> > Next
    >> > .MergeCells = False
    >> > .Cells(1).ColumnWidth = MergedCellRgWidth
    >> > .EntireRow.AutoFit
    >> > PossNewRowHeight = .RowHeight
    >> > .Cells(1).ColumnWidth = ActiveCellWidth
    >> > .MergeCells = True
    >> > .RowHeight = IIf(CurrentRowHeight > PossNewRowHeight, _
    >> > CurrentRowHeight, PossNewRowHeight)
    >> > End If
    >> > End With
    >> > End If
    >> > End Sub
    >> >
    >> > This works fine, just need it to be automatic. So I added this to the
    >> > worksheet:
    >> >
    >> > Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    >> > AutoFitMergedCellRowHeight
    >> > End Sub
    >> >
    >> > This mostly works. When I enter text in a merged cell which will
    >> > require
    >> > wrap then press enter, it does not automatically wrap. But when I go
    >> > back
    >> > and click on the cell, it wraps. What do I need to do so I don't have
    >> > to
    >> > go
    >> > back and click on the cell?
    >> >
    >> > Thanks,
    >> > Steve
    >> >
    >> >
    >> >

    >>
    >>
    >>




  8. #8
    SteveFerd
    Guest

    Re: newbie VBA help please

    Steve,
    Thanks for your help here, I really appreciate it.
    I do have the code in the sheet module, and the code is as follows:

    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Call AutoFitMergedCellRowHeight
    End Sub

    Nothing happens.

    When I added MsgBox "event happened" the macro still doesn't fire, but
    the msg box does appear as expected, so there must be an issue with the macro.

    The following code from Jim Rech is included in module 1:
    ''Simulates row height autofit for a merged cell if the active cell..
    '' is merged.
    '' has Wrap Text set.
    '' includes only 1 row.
    ''Unlike real autosizing the macro only increases row height
    '' (if needed). It does not reduce row height because another
    '' merged cell on the same row may needed a greater height
    '' than the active cell.
    Sub AutoFitMergedCellRowHeight()
    Dim CurrentRowHeight As Single, MergedCellRgWidth As Single
    Dim CurrCell As Range
    Dim ActiveCellWidth As Single, PossNewRowHeight As Single
    If ActiveCell.MergeCells Then
    With ActiveCell.MergeArea
    If .Rows.Count = 1 And .WrapText = True Then
    Application.ScreenUpdating = False
    CurrentRowHeight = .RowHeight
    ActiveCellWidth = ActiveCell.ColumnWidth
    For Each CurrCell In Selection
    MergedCellRgWidth = CurrCell.ColumnWidth + MergedCellRgWidth
    Next
    .MergeCells = False
    .Cells(1).ColumnWidth = MergedCellRgWidth
    .EntireRow.AutoFit
    PossNewRowHeight = .RowHeight
    .Cells(1).ColumnWidth = ActiveCellWidth
    .MergeCells = True
    .RowHeight = IIf(CurrentRowHeight > PossNewRowHeight, _
    CurrentRowHeight, PossNewRowHeight)
    End If
    End With
    End If
    End Sub

    Is this code not compatible with a change event?

    Thanks,
    Steve



    "STEVE BELL" wrote:

    > Steve,
    >
    > Make sure that the event macro is in the sheet module and not in a regular
    > module.
    > Here is another version (replace Macro6 with the name of your macro)
    > Remember that the change event is fired by entering anything into a cell and
    > that leaving that cell.
    > It does not fire if you select a cell.
    >
    > To target column 4 make sure your if statement
    > If target.column = 4 then
    >
    > Private Sub Worksheet_Change(ByVal Target As Range)
    > Call Macro6
    > End Sub
    >
    > If this doesn't work
    > add a Msgbox line to give you a signal
    > add a msgbox to the other macro to see if it gets called.
    >
    > Private Sub Worksheet_Change(ByVal Target As Range)
    > Msgbox "Event happened"
    > Call Macro6
    > End Sub
    >
    > If none of this helps, than show us your code and tell us where it is
    > located....
    > --
    > steveB
    >
    > Remove "AYN" from email to respond
    > "SteveFerd" <[email protected]> wrote in message
    > news:[email protected]...
    > > Thanks everyone for your help. I changed my worksheet event from a
    > > selection
    > > change to a change event. Now nothing happens, even when I go back and
    > > click
    > > on the cell after entering the text.
    > > Also a combination of a change event and an if statement targeting column
    > > 4
    > > didn't work.
    > > Any other ideas?
    > >
    > > "STEVE BELL" wrote:
    > >
    > >> If you want it to happen when you edit a cell,
    > >> use an event macro - place it in the worksheet module.
    > >>
    > >> Private Sub Worksheet_Change(ByVal Target As Range)
    > >> AutoFitMergedCellRowHeight
    > >> End Sub
    > >>
    > >> You can make the code more selective using if statements
    > >> target.address="$A$1"
    > >> target.row = 1
    > >> target.column =1
    > >> or use the intersect function
    > >>
    > >> write back if you need more help...
    > >> --
    > >> steveB
    > >>
    > >> Remove "AYN" from email to respond
    > >> "SteveFerd" <[email protected]> wrote in message
    > >> news:[email protected]...
    > >> > Hello, Thanks in advance for your help.
    > >> >
    > >> > Before I even ask my question, I am far from being a programer and
    > >> > don't
    > >> > really even know enough to be dangerous. I am developing a template
    > >> > for a
    > >> > group of 40 sales reps which will be used to track large accounts. The
    > >> > template requires the use of merged cells which will need to be able to
    > >> > use
    > >> > word wrap. I followed previous advice and use the following code from
    > >> > Jim
    > >> > Rech:
    > >> > ''Simulates row height autofit for a merged cell if the active cell..
    > >> > '' is merged.
    > >> > '' has Wrap Text set.
    > >> > '' includes only 1 row.
    > >> > ''Unlike real autosizing the macro only increases row height
    > >> > '' (if needed). It does not reduce row height because another
    > >> > '' merged cell on the same row may needed a greater height
    > >> > '' than the active cell.
    > >> > Sub AutoFitMergedCellRowHeight()
    > >> > Dim CurrentRowHeight As Single, MergedCellRgWidth As Single
    > >> > Dim CurrCell As Range
    > >> > Dim ActiveCellWidth As Single, PossNewRowHeight As Single
    > >> > If ActiveCell.MergeCells Then
    > >> > With ActiveCell.MergeArea
    > >> > If .Rows.Count = 1 And .WrapText = True Then
    > >> > Application.ScreenUpdating = False
    > >> > CurrentRowHeight = .RowHeight
    > >> > ActiveCellWidth = ActiveCell.ColumnWidth
    > >> > For Each CurrCell In Selection
    > >> > MergedCellRgWidth = CurrCell.ColumnWidth +
    > >> > MergedCellRgWidth
    > >> > Next
    > >> > .MergeCells = False
    > >> > .Cells(1).ColumnWidth = MergedCellRgWidth
    > >> > .EntireRow.AutoFit
    > >> > PossNewRowHeight = .RowHeight
    > >> > .Cells(1).ColumnWidth = ActiveCellWidth
    > >> > .MergeCells = True
    > >> > .RowHeight = IIf(CurrentRowHeight > PossNewRowHeight, _
    > >> > CurrentRowHeight, PossNewRowHeight)
    > >> > End If
    > >> > End With
    > >> > End If
    > >> > End Sub
    > >> >
    > >> > This works fine, just need it to be automatic. So I added this to the
    > >> > worksheet:
    > >> >
    > >> > Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    > >> > AutoFitMergedCellRowHeight
    > >> > End Sub
    > >> >
    > >> > This mostly works. When I enter text in a merged cell which will
    > >> > require
    > >> > wrap then press enter, it does not automatically wrap. But when I go
    > >> > back
    > >> > and click on the cell, it wraps. What do I need to do so I don't have
    > >> > to
    > >> > go
    > >> > back and click on the cell?
    > >> >
    > >> > Thanks,
    > >> > Steve
    > >> >
    > >> >
    > >> >
    > >>
    > >>
    > >>

    >
    >
    >


  9. #9
    STEVE BELL
    Guest

    Re: newbie VBA help please

    If the msgbox shows, than the event is firing OK.
    Put a msgbox at the begining of the called macro to
    see if it is being called.

    If it is not being called than check the Call statement in the event macro.
    Make sure the macro name is spelled correctly (I do this with copy/paste)
    Try removing the word "Call".
    Make sure that this macro is in a standard module.
    Compile your workbook and check for any issues with any of your code.

    Also look at the response from malik641...

    hth

    --
    steveB

    Remove "AYN" from email to respond
    "SteveFerd" <[email protected]> wrote in message
    news:[email protected]...
    > Steve,
    > Thanks for your help here, I really appreciate it.
    > I do have the code in the sheet module, and the code is as follows:
    >
    > Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    > Call AutoFitMergedCellRowHeight
    > End Sub
    >
    > Nothing happens.
    >
    > When I added MsgBox "event happened" the macro still doesn't fire, but
    > the msg box does appear as expected, so there must be an issue with the
    > macro.
    >
    > The following code from Jim Rech is included in module 1:
    > ''Simulates row height autofit for a merged cell if the active cell..
    > '' is merged.
    > '' has Wrap Text set.
    > '' includes only 1 row.
    > ''Unlike real autosizing the macro only increases row height
    > '' (if needed). It does not reduce row height because another
    > '' merged cell on the same row may needed a greater height
    > '' than the active cell.
    > Sub AutoFitMergedCellRowHeight()
    > Dim CurrentRowHeight As Single, MergedCellRgWidth As Single
    > Dim CurrCell As Range
    > Dim ActiveCellWidth As Single, PossNewRowHeight As Single
    > If ActiveCell.MergeCells Then
    > With ActiveCell.MergeArea
    > If .Rows.Count = 1 And .WrapText = True Then
    > Application.ScreenUpdating = False
    > CurrentRowHeight = .RowHeight
    > ActiveCellWidth = ActiveCell.ColumnWidth
    > For Each CurrCell In Selection
    > MergedCellRgWidth = CurrCell.ColumnWidth +
    > MergedCellRgWidth
    > Next
    > .MergeCells = False
    > .Cells(1).ColumnWidth = MergedCellRgWidth
    > .EntireRow.AutoFit
    > PossNewRowHeight = .RowHeight
    > .Cells(1).ColumnWidth = ActiveCellWidth
    > .MergeCells = True
    > .RowHeight = IIf(CurrentRowHeight > PossNewRowHeight, _
    > CurrentRowHeight, PossNewRowHeight)
    > End If
    > End With
    > End If
    > End Sub
    >
    > Is this code not compatible with a change event?
    >
    > Thanks,
    > Steve
    >
    >
    >
    > "STEVE BELL" wrote:
    >
    >> Steve,
    >>
    >> Make sure that the event macro is in the sheet module and not in a
    >> regular
    >> module.
    >> Here is another version (replace Macro6 with the name of your macro)
    >> Remember that the change event is fired by entering anything into a cell
    >> and
    >> that leaving that cell.
    >> It does not fire if you select a cell.
    >>
    >> To target column 4 make sure your if statement
    >> If target.column = 4 then
    >>
    >> Private Sub Worksheet_Change(ByVal Target As Range)
    >> Call Macro6
    >> End Sub
    >>
    >> If this doesn't work
    >> add a Msgbox line to give you a signal
    >> add a msgbox to the other macro to see if it gets called.
    >>
    >> Private Sub Worksheet_Change(ByVal Target As Range)
    >> Msgbox "Event happened"
    >> Call Macro6
    >> End Sub
    >>
    >> If none of this helps, than show us your code and tell us where it is
    >> located....
    >> --
    >> steveB
    >>
    >> Remove "AYN" from email to respond
    >> "SteveFerd" <[email protected]> wrote in message
    >> news:[email protected]...
    >> > Thanks everyone for your help. I changed my worksheet event from a
    >> > selection
    >> > change to a change event. Now nothing happens, even when I go back and
    >> > click
    >> > on the cell after entering the text.
    >> > Also a combination of a change event and an if statement targeting
    >> > column
    >> > 4
    >> > didn't work.
    >> > Any other ideas?
    >> >
    >> > "STEVE BELL" wrote:
    >> >
    >> >> If you want it to happen when you edit a cell,
    >> >> use an event macro - place it in the worksheet module.
    >> >>
    >> >> Private Sub Worksheet_Change(ByVal Target As Range)
    >> >> AutoFitMergedCellRowHeight
    >> >> End Sub
    >> >>
    >> >> You can make the code more selective using if statements
    >> >> target.address="$A$1"
    >> >> target.row = 1
    >> >> target.column =1
    >> >> or use the intersect function
    >> >>
    >> >> write back if you need more help...
    >> >> --
    >> >> steveB
    >> >>
    >> >> Remove "AYN" from email to respond
    >> >> "SteveFerd" <[email protected]> wrote in message
    >> >> news:[email protected]...
    >> >> > Hello, Thanks in advance for your help.
    >> >> >
    >> >> > Before I even ask my question, I am far from being a programer and
    >> >> > don't
    >> >> > really even know enough to be dangerous. I am developing a template
    >> >> > for a
    >> >> > group of 40 sales reps which will be used to track large accounts.
    >> >> > The
    >> >> > template requires the use of merged cells which will need to be able
    >> >> > to
    >> >> > use
    >> >> > word wrap. I followed previous advice and use the following code
    >> >> > from
    >> >> > Jim
    >> >> > Rech:
    >> >> > ''Simulates row height autofit for a merged cell if the active
    >> >> > cell..
    >> >> > '' is merged.
    >> >> > '' has Wrap Text set.
    >> >> > '' includes only 1 row.
    >> >> > ''Unlike real autosizing the macro only increases row height
    >> >> > '' (if needed). It does not reduce row height because another
    >> >> > '' merged cell on the same row may needed a greater height
    >> >> > '' than the active cell.
    >> >> > Sub AutoFitMergedCellRowHeight()
    >> >> > Dim CurrentRowHeight As Single, MergedCellRgWidth As Single
    >> >> > Dim CurrCell As Range
    >> >> > Dim ActiveCellWidth As Single, PossNewRowHeight As Single
    >> >> > If ActiveCell.MergeCells Then
    >> >> > With ActiveCell.MergeArea
    >> >> > If .Rows.Count = 1 And .WrapText = True Then
    >> >> > Application.ScreenUpdating = False
    >> >> > CurrentRowHeight = .RowHeight
    >> >> > ActiveCellWidth = ActiveCell.ColumnWidth
    >> >> > For Each CurrCell In Selection
    >> >> > MergedCellRgWidth = CurrCell.ColumnWidth +
    >> >> > MergedCellRgWidth
    >> >> > Next
    >> >> > .MergeCells = False
    >> >> > .Cells(1).ColumnWidth = MergedCellRgWidth
    >> >> > .EntireRow.AutoFit
    >> >> > PossNewRowHeight = .RowHeight
    >> >> > .Cells(1).ColumnWidth = ActiveCellWidth
    >> >> > .MergeCells = True
    >> >> > .RowHeight = IIf(CurrentRowHeight > PossNewRowHeight,
    >> >> > _
    >> >> > CurrentRowHeight, PossNewRowHeight)
    >> >> > End If
    >> >> > End With
    >> >> > End If
    >> >> > End Sub
    >> >> >
    >> >> > This works fine, just need it to be automatic. So I added this to
    >> >> > the
    >> >> > worksheet:
    >> >> >
    >> >> > Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    >> >> > AutoFitMergedCellRowHeight
    >> >> > End Sub
    >> >> >
    >> >> > This mostly works. When I enter text in a merged cell which will
    >> >> > require
    >> >> > wrap then press enter, it does not automatically wrap. But when I
    >> >> > go
    >> >> > back
    >> >> > and click on the cell, it wraps. What do I need to do so I don't
    >> >> > have
    >> >> > to
    >> >> > go
    >> >> > back and click on the cell?
    >> >> >
    >> >> > Thanks,
    >> >> > Steve
    >> >> >
    >> >> >
    >> >> >
    >> >>
    >> >>
    >> >>

    >>
    >>
    >>




  10. #10
    SteveFerd
    Guest

    Re: newbie VBA help please

    Ok, I now agree, merged cells are definitely evil. I added
    ACTIVECELL.OFFSET(-1, 0).SELECT[/B] right before the first IF statement and
    get this
    "compile error: Wrong number of arguments or invalid property assignment"
    the *.select* in the added code is highlighted.

    Thanks everyone for your time, I appreciate it.

    "malik641" wrote:

    >
    > Okay, this works.
    > Try this:
    >
    > Make sure you keep the
    >
    > PRIVATE SUB WORKSHEET_CHANGE(BYVAL TARGET AS RANGE)
    > AUTOFITMERGEDCELLROWHEIGHT
    > END SUB
    >
    > That Jim and Steve told you to change.
    >
    > and in your *Sub AutoFitMergedCellRowHeight () * macro right above the
    > first if statement add:
    >
    > ACTIVECELL.OFFSET(-1, 0).SELECT[/B]
    >
    > AND HERE IS WHAT YOU END UP WITH.
    >
    >
    >
    > SUB AUTOFITMERGEDCELLROWHEIGHT()
    >
    > DIM CURRENTROWHEIGHT AS SINGLE, MERGEDCELLRGWIDTH AS SINGLE
    > DIM CURRCELL AS RANGE
    > DIM ACTIVECELLWIDTH AS SINGLE, POSSNEWROWHEIGHT AS SINGLE
    >
    > [B]ACTIVECELL.OFFSET(-1, 0).SELECT
    >
    > If ActiveCell.MergeCells Then
    > With ActiveCell.MergeArea
    > If .Rows.Count = 1 And .WrapText = True Then
    > Application.ScreenUpdating = False
    > CurrentRowHeight = .RowHeight
    > ActiveCellWidth = ActiveCell.ColumnWidth
    > For Each CurrCell In Selection
    > MergedCellRgWidth = CurrCell.ColumnWidth + MergedCellRgWidth
    > Next
    > .MergeCells = False
    > .Cells(1).ColumnWidth = MergedCellRgWidth
    > .EntireRow.AutoFit
    > PossNewRowHeight = .RowHeight
    > .Cells(1).ColumnWidth = ActiveCellWidth
    > .MergeCells = True
    > .RowHeight = IIf(CurrentRowHeight > PossNewRowHeight, _
    > CurrentRowHeight, PossNewRowHeight)
    > End If
    > End With
    > End If
    >
    > End Sub
    >
    > PRIVATE SUB WORKSHEET_CHANGE(BYVAL TARGET AS RANGE)
    > AUTOFITMERGEDCELLROWHEIGHT
    > END SUB
    > ---------------------------------------------------
    >
    > Now after you enter the text and you press enter it will change it for
    > you automatically and remain to be the cell that you entered the text
    > in.
    >
    > Hope this is what you were looking for!
    >
    >
    > --
    > malik641
    >
    >
    > ------------------------------------------------------------------------
    > malik641's Profile: http://www.excelforum.com/member.php...o&userid=24190
    > View this thread: http://www.excelforum.com/showthread...hreadid=388557
    >
    >


  11. #11
    Forum Contributor
    Join Date
    06-10-2005
    Location
    Central Jersey
    Posts
    117
    Yeah you will get that if you have ".Select[/B]" in your code. make sure it says ".Select" WITHOUT the "[/B]".

    If you have that "[/B]" in your CODE, delete it and it SHOULD work fine...at least it does for me...If it STILL happens...then please post your code again and I'll check it out.

    Quote Originally Posted by SteveFerd
    Ok, I now agree, merged cells are definitely evil. I added
    ACTIVECELL.OFFSET(-1, 0).SELECT[/B] right before the first IF statement and
    get this
    "compile error: Wrong number of arguments or invalid property assignment"
    the *.select* in the added code is highlighted.

    Thanks everyone for your time, I appreciate it.

  12. #12
    SteveFerd
    Guest

    Re: newbie VBA help please

    Thanks malik, that did it. I really appreciate the help!



    "malik641" wrote:

    >
    > Yeah you will get that if you have ".Select[/B]" in your code. make sure
    > it says ".Select" WITHOUT the "[/B]".
    >
    > If you have that "[/B]" in your CODE, delete it and it SHOULD work
    > fine...at least it does for me...If it STILL happens...then please post
    > your code again and I'll check it out.
    >
    > SteveFerd Wrote:
    > > Ok, I now agree, merged cells are definitely evil. I added
    > > ACTIVECELL.OFFSET(-1, 0).SELECT[/B] right before the first IF statement
    > > and
    > > get this
    > > "compile error: Wrong number of arguments or invalid property
    > > assignment"
    > > the *.select* in the added code is highlighted.
    > >
    > > Thanks everyone for your time, I appreciate it.
    > >
    > >

    >
    >
    > --
    > malik641
    >
    >
    > ------------------------------------------------------------------------
    > malik641's Profile: http://www.excelforum.com/member.php...o&userid=24190
    > View this thread: http://www.excelforum.com/showthread...hreadid=388557
    >
    >


  13. #13
    Forum Contributor
    Join Date
    06-10-2005
    Location
    Central Jersey
    Posts
    117
    Awesome! Glad to hear it's working. Thanks for the feedback.

    Quote Originally Posted by SteveFerd
    Thanks malik, that did it. I really appreciate the help!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1