Dynamically changing column widths in Crystal XI

[RWineJr]

thread222-814729

I had to create some dynamic columns based on an unknown number of fields being used. Here's the Crystal Formula I set up in the header and suppressed. I hope it helps. We knew there would never be more than 10 columns, but typically less than 10 columns. It will dynamically set the empty ones to a 0 width and resize the remaining ones and set the remaining ones. It's complicated, ugly and a royal pain, but it gets the job done and might save someone a little bit of headache. The reason for the nasty formula at the end is simply to see the column widths while making the formula. Check the formula button and use this code for the X location changing the index from 1 to 2 to 3 .... based on which item you wish to show:

Shared ColumnsLeft(10) As Number
Formula = ColumnsLeft(1)

and this for the width:

Shared ColumnWidth As Number
Formula = ColumnWidth

Once you have it working, just suppress the formula and it's all good.

The Shared variables are so that you can set the column widths and locations in the size and position of the particular field. Simply make the formula use BASIC syntax and share each of the variables and you can then dynamically position as needed.

Shared TotalColumns As Number
Shared ColumnWidth As Number
Shared ColumnsLeft(10) As Number
Dim LoopIndex As Number
Dim StaticColumnWidth As Number
Dim Pixels As Number
Pixels = 1440
TotalColumns = 0

If (Not IsNull({Eval_EvaluationReportShared;1.Header01})) Then TotalColumns = TotalColumns + 1
If (Not IsNull({Eval_EvaluationReportShared;1.Header02})) Then TotalColumns = TotalColumns + 1
If (Not IsNull({Eval_EvaluationReportShared;1.Header03})) Then TotalColumns = TotalColumns + 1
If (Not IsNull({Eval_EvaluationReportShared;1.Header04})) Then TotalColumns = TotalColumns + 1
If (Not IsNull({Eval_EvaluationReportShared;1.Header05})) Then TotalColumns = TotalColumns + 1
If (Not IsNull({Eval_EvaluationReportShared;1.Header06})) Then TotalColumns = TotalColumns + 1
If (Not IsNull({Eval_EvaluationReportShared;1.Header07})) Then TotalColumns = TotalColumns + 1
If (Not IsNull({Eval_EvaluationReportShared;1.Header08})) Then TotalColumns = TotalColumns + 1
If (Not IsNull({Eval_EvaluationReportShared;1.Header09})) Then TotalColumns = TotalColumns + 1
If (Not IsNull({Eval_EvaluationReportShared;1.Header10})) Then TotalColumns = TotalColumns + 1

If( TotalColumns > 0) Then
ColumnWidth = 5.50 / TotalColumns 'The Left Side Of The Mean Is 9.200. The Right Side Of Question Text Is 3.200, Thusly 6.000. We Can Set The ColumnWidth = 6.000 / TotalColumns.
End If

'StaticColumnWidth = ColumnWidth 'Just Holds To Keep A Possible Accumulator Problem From Happening.
' The New Columns Will Become The New Left Based On Crystal's .Left Function Moving A Number Of Pixels To The Right. Also, The Distance Being In Pixels Makes Us Multiply It By 1440.
'ColumnsLeft(1) = 0 'Column Number 1 NEVER Changes Regardless Of The Number Of Columns.
For LoopIndex = 1 To 10
'If (TotalColumns >= LoopIndex) Then
'ColumnsLeft(LoopIndex) = 0
ColumnsLeft(LoopIndex) = (LoopIndex - 1) * ColumnWidth * Pixels * 1.05 'The New Column Width Is Calculated, Remove The Original .58 Width, Then Multiply By Pixels Per Inch (1440).
If (LoopIndex > TotalColumns) Then ColumnsLeft(LoopIndex) = 0 'This Is To Keep The Row From Getting Too Wide.
Next LoopIndex
'Make The Pixel Conversion Now (Pixels Are 1440 Per Inch Which The Calculations Above Are Made On).
ColumnWidth = ColumnWidth * Pixels * 0.50
If (TotalColumns = 0) Then ColumnWidth = 390 'This Is The Default Size For 10.
Formula = "TotalColumns: " & ToText(TotalColumns, "#0") & ", ColumnWidth: " & ToText(ColumnWidth, "#0.000") & ", CL(1) = " & ToText(ColumnsLeft(1), "0") & ", CL(2) = " & ToText(ColumnsLeft(2), "0") & ", CL(3) = " & ToText(ColumnsLeft(3), "0") & ", CL(4) = " & ToText(ColumnsLeft(4), "0") & ", CL(5) = " & ToText(ColumnsLeft(5), "0") & ", CL(6) = " & ToText(ColumnsLeft(6), "0") & ", CL(7) = " & ToText(ColumnsLeft(7), "0") & ", CL(8) = " & ToText(ColumnsLeft(8), "0") & ", CL(9) = " & ToText(ColumnsLeft(9), "0") & ", CL(10) = " & ToText(ColumnsLeft(10), "0")