locked
Text Wrap in Merged Cells RRS feed

  • Question

  • I'm programming in VB.net, and trying to wrap text in merged cells from a button click event.  It is rather simple to wrap text when the active cell is not merged, here is the code that is working for me from a button click event.

            Dim ActvCell As Range
    
            'Declare ActvCell
            ActvCell = Globals.ThisAddIn.Application.ActiveCell
    
            'Wrap text
            ActvCell.WrapText = True
    

    The problem is, this does not work if the active cell consists of merged cells.

    I found VBA code that seems to work from a macro. So, I copied that VBA code and pasted it into Visual Studios. Then I rewrote the errors that popped up, so that there are no more errors showing. But it does not work. Here is that modified VBA code:

            Dim ActvCell As Range
    
            ActvCell = Globals.ThisAddIn.Application.ActiveCell
    
            Dim CurrentRowHeight As Single, MergedCellRgWidth As Single
    
            Dim CurrCell As Range, RangeWidth As Single
    
            Dim ActiveCellWidth As Single, PossNewRowHeight As Single
            If ActvCell.MergeCells Then
                With ActvCell.MergeArea
                    If .Rows.Count = 1 And .WrapText = True Then
                        Globals.ThisAddIn.Application.ScreenUpdating = False
                        CurrentRowHeight = .RowHeight
                        ActiveCellWidth = ActvCell.ColumnWidth
    
                        RangeWidth = .Width
    
                        For Each CurrCell In Globals.ThisAddIn.Application.Selection
                            MergedCellRgWidth = CurrCell.ColumnWidth + MergedCellRgWidth
                        Next
                        .MergeCells = False
                        .Cells(1).ColumnWidth = MergedCellRgWidth
    
                        While .Cells(1).Width < RangeWidth
                            .Cells(1).ColumnWidth = .Cells(1).ColumnWidth + 0.5
                        End While
                        .Cells(1).ColumnWidth = .Cells(1).ColumnWidth - 0.5
    
                        .EntireRow.AutoFit()
                        PossNewRowHeight = .RowHeight
                        .Cells(1).ColumnWidth = ActiveCellWidth
                        .MergeCells = True
                        .RowHeight = IIf(CurrentRowHeight > PossNewRowHeight, _
                         CurrentRowHeight, PossNewRowHeight)
                    End If
                End With
            End If
    Any thoughts...Thanks!


    Friday, February 21, 2014 11:07 PM

Answers

  • Hi,

    >>The problem is, this does not work if the active cell consists of merged cells.<<

    Firstly, I try to use the first code to set the WrapText property of a Merged Cell and it works well. In fact, we could wrap the merged cells in format setting.

    So how do you select “the active cell consists of merged cells”?

    If you select some cells which contains a merged cell, I think the code will not work since you are using ActiveCell object. ActiveCell Property returns a Range object that represents the active cell in the active window or in the specified window. It cannot be used to represent more than one cells. If you want to select some cells, you need to use the Selection Property.

    When I use the selection property to do that with VB.Net, it works well.

    Private Sub WrapCells()
    Dim sel As Excel.Range
    
        'Declare Selection
        sel = Globals.ThisAddIn.Application.Selection
    
        'Wrap text
        sel.WrapText = True
    End Sub

    A link below will help you to understand the ActiveCell property and Selection property:

    The ActiveCell and Selection Properties


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Monday, February 24, 2014 8:35 AM
  • Thanks for your reply. I have taken a slightly different route, and it seems to be working...here is the code:

            Dim ActvCell As Range
            Dim MrgdSelection As Range
            Dim CurrentRowHeight As Single
            Dim MergedCellRgWidth As Single
            Dim CurrCell As Range
            Dim RangeWidth As Single
            Dim ActiveCellWidth As Single
            Dim PossNewRowHeight As Single
    
            MrgdSelection = Globals.ThisAddIn.Application.Selection
    
            ActvCell = Globals.ThisAddIn.Application.ActiveCell
            'Set cell property to wrap text
            ActvCell.WrapText = True
    
    
            If ActvCell.MergeCells Then
                With ActvCell.MergeArea
                    If .Rows.Count = 1 And .WrapText = True Then
                        Globals.ThisAddIn.Application.ScreenUpdating = False
                        CurrentRowHeight = .RowHeight
                        ActiveCellWidth = ActvCell.ColumnWidth
    
                        RangeWidth = .Width
    
                        For Each CurrCell In MrgdSelection
                            MergedCellRgWidth = CurrCell.ColumnWidth + MergedCellRgWidth
                        Next
                        .MergeCells = False
                        .Cells(1).ColumnWidth = MergedCellRgWidth
    
                        While .Cells(1).Width < RangeWidth
                            .Cells(1).ColumnWidth = .Cells(1).ColumnWidth + 1.0
                        End While
                        .Cells(1).ColumnWidth = .Cells(1).ColumnWidth - 1.0
    
                        .EntireRow.AutoFit()
                        PossNewRowHeight = .RowHeight
                        .Cells(1).ColumnWidth = ActiveCellWidth
                        .MergeCells = True
                        .RowHeight = IIf(CurrentRowHeight > PossNewRowHeight, _
                         CurrentRowHeight, PossNewRowHeight)
                        Globals.ThisAddIn.Application.ScreenUpdating = True
                    End If
                End With
            End If
    
            'Select active cell
            ActvCell.Select()

    The lines that are bolded, italicized, and underlined are the changes that seem to have solved the problem that I was describing in my previous post.

    Tuesday, February 25, 2014 12:31 AM

All replies

  • After performing some testing, I believe the code is getting hung up at this line...

             For Each CurrCell In Globals.ThisAddIn.Application.Selection
    

    Here is how the line was originally, in the VBA code...

             For Each CurrCell In Selection
    
    But "Selection" showed up as an error in VB.net. So, I modified it as shown above. Apparently, my modification is wrong, but I'm not sure where to go from here.  Hopefully everything else after this line is correct.

    Saturday, February 22, 2014 2:25 AM
  • Hi,

    >>The problem is, this does not work if the active cell consists of merged cells.<<

    Firstly, I try to use the first code to set the WrapText property of a Merged Cell and it works well. In fact, we could wrap the merged cells in format setting.

    So how do you select “the active cell consists of merged cells”?

    If you select some cells which contains a merged cell, I think the code will not work since you are using ActiveCell object. ActiveCell Property returns a Range object that represents the active cell in the active window or in the specified window. It cannot be used to represent more than one cells. If you want to select some cells, you need to use the Selection Property.

    When I use the selection property to do that with VB.Net, it works well.

    Private Sub WrapCells()
    Dim sel As Excel.Range
    
        'Declare Selection
        sel = Globals.ThisAddIn.Application.Selection
    
        'Wrap text
        sel.WrapText = True
    End Sub

    A link below will help you to understand the ActiveCell property and Selection property:

    The ActiveCell and Selection Properties


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Monday, February 24, 2014 8:35 AM
  • Thanks for your reply. I have taken a slightly different route, and it seems to be working...here is the code:

            Dim ActvCell As Range
            Dim MrgdSelection As Range
            Dim CurrentRowHeight As Single
            Dim MergedCellRgWidth As Single
            Dim CurrCell As Range
            Dim RangeWidth As Single
            Dim ActiveCellWidth As Single
            Dim PossNewRowHeight As Single
    
            MrgdSelection = Globals.ThisAddIn.Application.Selection
    
            ActvCell = Globals.ThisAddIn.Application.ActiveCell
            'Set cell property to wrap text
            ActvCell.WrapText = True
    
    
            If ActvCell.MergeCells Then
                With ActvCell.MergeArea
                    If .Rows.Count = 1 And .WrapText = True Then
                        Globals.ThisAddIn.Application.ScreenUpdating = False
                        CurrentRowHeight = .RowHeight
                        ActiveCellWidth = ActvCell.ColumnWidth
    
                        RangeWidth = .Width
    
                        For Each CurrCell In MrgdSelection
                            MergedCellRgWidth = CurrCell.ColumnWidth + MergedCellRgWidth
                        Next
                        .MergeCells = False
                        .Cells(1).ColumnWidth = MergedCellRgWidth
    
                        While .Cells(1).Width < RangeWidth
                            .Cells(1).ColumnWidth = .Cells(1).ColumnWidth + 1.0
                        End While
                        .Cells(1).ColumnWidth = .Cells(1).ColumnWidth - 1.0
    
                        .EntireRow.AutoFit()
                        PossNewRowHeight = .RowHeight
                        .Cells(1).ColumnWidth = ActiveCellWidth
                        .MergeCells = True
                        .RowHeight = IIf(CurrentRowHeight > PossNewRowHeight, _
                         CurrentRowHeight, PossNewRowHeight)
                        Globals.ThisAddIn.Application.ScreenUpdating = True
                    End If
                End With
            End If
    
            'Select active cell
            ActvCell.Select()

    The lines that are bolded, italicized, and underlined are the changes that seem to have solved the problem that I was describing in my previous post.

    Tuesday, February 25, 2014 12:31 AM