Modify Report Header Height And Contents With VBA Upon Condition

[mordja]

Hi,

I have three summary fields in my report header, each has a label and a ytd and mtd figure.

client mtd ytd
txtSumOfSwaps_YTD mtd ytd
txtSumOfListed_Derivs mtd ytd

client always has non zero values for mtd and ytd. swaps and derivs dont. If swaps has 0 values I want to hide it and move derivs to its place and reduce the header size, if derivs has 0 values I want to hide it and reduce the header size. If swaps and derivs both = 0 I want to hide them both and reduce the header size.

I have no problem making them visible/invisible but when I try and set the position using top they all sit at the top of the header and overlap each other, ignoring the values of top I assign. I havent quite finished but even the initial use of top doesnt work, is top the best way to use absolute position Suggestions ?

Private Sub GroupHeader1_Format(Cancel As Integer, FormatCount As Integer)
'intformatCount 1 = swaps 0, 2 = derivs 0, 3 = deriv and swaps = 0
Dim intFormatCount As Integer
intFormatCount = 0

Me.txtClientMTDtotal.Top = 0.053
Me.txtClientYTDtotal.Top = 0.053
Me.lblClient.Top = 0.053
Me.txtSumOfSwaps_MTD.Top = 0.397
Me.txtSumOfSwaps_YTD.Top = 0.397
Me.lblSwaps.Top = 0.397
Me.txtSumOfListed_Derivs_MTD.Top = 0.741
Me.txtSumOfListed_Derivs_YTD.Top = 0.741
Me.lblDerivs.Top = 0.741


If Me.txtSumOfSwaps_MTD = 0 And Me.txtSumOfSwaps_YTD = 0 Then
    Me.txtSumOfSwaps_MTD.Visible = False
    Me.txtSumOfSwaps_YTD.Visible = False
    Me.lblSwaps.Visible = False
    intFormatCount = 1
Else
    Me.txtSumOfSwaps_MTD.Visible = False
    Me.txtSumOfSwaps_YTD.Visible = False
    Me.lblSwaps.Visible = False
End If

If txtSumOfListed_Derivs_MTD = 0 And Me.txtSumOfListed_Derivs_YTD = 0 Then
    Me.txtSumOfListed_Derivs_MTD.Visible = False
    Me.txtSumOfListed_Derivs_YTD.Visible = False
    Me.lblDerivs.Visible = False
    If intFormatCount = 0 Then
        intFormatCount = 2
    Else
        intFormatCount = 3
    End If
Else
    Me.txtSumOfListed_Derivs_MTD.Visible = True
    Me.txtSumOfListed_Derivs_YTD.Visible = True
    Me.lblDerivs.Visible = True
End If

'move derivs up to swaps position
If intFormatCount = 2 Then
    Me.txtSumOfListed_Derivs_MTD.Top = 0.397
    Me.txtSumOfListed_Derivs_YTD.Top = 0.397
    Me.lblDerivs.Top = 0.397
End If

Thanks

Mordja

 

[VBslammer]

You have to use inch format, so multiply your values times 1440 (twips per inch).

VBSlammer

"You just have to know which screws to turn." - Professor Bob

 

[mordja]

VBSlammer,

What are twips ??

I made the mistake of looking at the value of top while in design view and applying that. I tried using a multiplier of 1440 but got an error saying control was to large for area. I assumed that was because my measurements are in centimetres rather than inches and something like 1440/2.5 gave a pretty close result. In the end I just ran some code to print out the values for top for the three different controls and the height of the header and used that.

Thanks for pointing me in the right direction.

Mordja