Changing Row Height for all fields in a row (1 Viewer)

[Swatkins]

Good afternoon,

I have an existing report with a number of fields arranged horizontally in a tabular fashion in the detail section. The first field is an identifying line of text and the remaining fields are numerical calculations. The report is basically a simple profitability estimate report for a number of different products.

Behind the fields of data is another text box that is blank, which stretches across all the horizontal fields, and which changes background color using conditional formatting. When printing, this colors the whole row according to that criteria (it changes color based on the relative profitability of each product in the report).

The problem I'm having is that the first line of identifying text can sometimes overflow.

I've set the Can Grow property to Yes for this and all other controls in the Detail section. But the result is sometimes some funny-looking formatting. The identifier (like "Product XYZ with a really long description") will word-wrap, but every other field in that row maintains their own height. In particular, this makes the coloring of the conditionally formatted row look off, because it provides only one line's worth of color, even though the product description has wrapped to two lines.

Does anyone know how I can resize all the controls in the Detail section based on the height of the tallest control, and keep the whole thing dynamic?

Things I've tried: I've tried putting some VBA in either the OnFormat or the OnPrint events that iterates through the controls in the section and resets their heights. In the "OnFormat" event, nothing happens (no errors and no apparent changes in the appearance of the report). In the "OnPrint" event, I get an error that says the height property cannot be changed during a print or print preview.

The VBA I tried using was as follows:

Dim ctlIt As Control
Dim lngMaxHeight As Long
For Each ctlIt In Me.Controls
If ctlIt.ControlType = acTextBox Then
If ctlIt.Height > lngMaxHeight Then
lngMaxHeight = ctlIt.Height
End If
End If
Next ctlIt
For Each ctlIt In Me.Controls
If ctlIt.ControlType = acTextBox Then
ctlIt.Height = lngMaxHeight
End If
Next ctlIt

Thanks in advance for any help or insight!

Swatkins

 

[GinaWhipp]

This should work...

Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)

'Duane Hookom 3.18.2008
    
    Dim intMaxHeight As Integer
    Dim ctl As Control
    'Find highest control in Detail section that has a tag property of "Border"
        For Each ctl In Me.Section(0).Controls
            If ctl.Tag = "Border" Then
                If ctl.Height > intMaxHeight Then
                    intMaxHeight = ctl.Height
                End If
            End If
        Next
        
        'Draw a box around each control in Detail that has a tag property of "Border"
        For Each ctl In Me.Section(0).Controls
            If ctl.Tag = "Border" Then
                Me.Line (ctl.Left, ctl.Top)-Step(ctl.Width, intMaxHeight), vbBlack, B
                'Me.DrawWidth = 6  'play with this number to make line thicker
        End If
        Next

End Sub

...you would just need the resize portion.