grow label height with textbox height....

[wiklendt]

hey,

i have some VBA which is designed to find the maximum height of label and textbox control (i have changed the label into a textbox, but its function remains as a label: this was to faciliate the max height idea but i don't know that it's helped, actually), and then make the shorter one match the taller one such that the solid colour fill of the controls are nicely as tall as one another.

now, the VBA i'm using (found externally via google, modified slightly for my own purposes) i can get it to do the fill, but not the maximum height thing.

the logic is good (i think), but i'm suspicious whether the "OnFormat" event is the correct event to put this code - i think it's drawing the 'line' BEFORE the field is grown, and thus each field colour fill ends up looking just one line tall... (see attachment). the text is showing fine, it's just the height of the drawn line.

the other thing i was thinking was maybe my "find maximum height" code was incorrect - though i personally can find no flaw in it.

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
[COLOR=SeaGreen]'this code from http://www.eggheadcafe.com/software/aspnet/32844884/label-growing-problems.aspx
'major contributor: duanehooko[/COLOR]

Dim intMaxHeight As Integer
Dim ctl As Control
Dim lngColourLabel, lngColourText As Long

lngColourLabel = rgb(204, 255, 153) [COLOR=SeaGreen] 'pale green[/COLOR]
lngColourText = rgb(236, 236, 236)[COLOR=SeaGreen] 'pale grey[/COLOR]
[COLOR=YellowGreen]
[COLOR=SeaGreen]'Find tallest control in Detail section[/COLOR][/COLOR]
For Each ctl In Me.Section(0).Controls
    If ctl.Height > intMaxHeight Then
        intMaxHeight = ctl.Height
    End If
Next

Me.DrawStyle = 6 [COLOR=SeaGreen]'transparent border, solid fill[/COLOR]

[COLOR=SeaGreen]'Draw a box around each control in Detail according to its tag property[/COLOR]
For Each ctl In Me.Section(0).Controls
    If ctl.Tag = "label" Then
        Me.Line (ctl.Left, ctl.Top)-Step(ctl.Width, intMaxHeight), lngColourLabel, BF
    End If

    If ctl.Tag = "text" Then
        Me.Line (ctl.Left, ctl.Top)-Step(ctl.Width, intMaxHeight), lngColourText, BF
    End If
Next

End Sub

any help will be appreciated. thanks.

just one last note: the label sometimes (you can see in the pdf) is the taller of the two, which is why i make VBA find the tallest control in the section before applying that height to both controls there.

 

[wiklendt]

ok, solved my own problem!!

the VBA worked in the "OnPrint" event :-D

hope this now will help anyone else trying to do this - it's a very elegant solution!

 

[wiklendt]

ok actually, here's something that i now want to do and am not sure how to go about it.

with the code above, this is to exist in several subreports i have in my database. i want to make a module with a "Function CtrlMaxHeight" to call in each OnPrint event where i want this funciton to work, BUT, when i put the code in (copy/paste), then try to run it by print previewing the report, access tells me i can't use "Me." in the code... how do i get around this so that i don't have to update it for every subreport i make?

 

[wiklendt]

ok, i found this thread which seems to be the only page on the entire internet which is most closely resembling what i need. however, i still am not sure how to alter my code based on that info. i tried using "Call CtrlMaxHeight(Me)" and "Call CtrlMaxHeight(Me.Name)" but i think i'm doing it wrong...?

i will need the module code to work for several subreports at the same time (one parent report - for now!). i don't know if this changes "index"-ing settings or whether i have to specify anything there...

also, i don't want to have to explicitly name each report in the code. i've tried using some generic code, like Reports(All) etc, though my knowledge in this is limited and have not been able to find anything useful in Help. (i am probably not using the right keywords to get to the help i need... "Reports" is very general a term in the help files!) also, the '.Line' doesn't seem to work when it's preceded with anything other than "Me" (debugger changes the line to red text and says that and equal sing is expected instead of the dash before the "step" keyword...

Plus, if my only option is to explicitly reference the reports, i'm not sure how to keep it to a general "all contols in this report's detail section"...

i can have the module code in the report itself, and it works there, but i would really prefer to have this 'external module'... kind of like when you use cascading style sheets instead of html when you script styling in web pages...

thanks again to anyone who can shed some light on this.

 

[wiklendt]

oh, i have also tried making the Funciton into a "Public Sub", but that didn't change its dislike for "Me" (no pun intended LOL)

 

[patatrac]

Hi for all
help me please , this code no work for my file,
is possible have an attached exaple file...
thanks

 

[wiklendt]

patatrac,

I have just gone back and found the database where i was trying to get this to work and am sorry to say i never did manage to get it to work. If you do discover a way, please post your success and details to the thread. Good luck!

Agnieszka.

 

[ChrisO]

The equivalent would be…

Behind the Report:-

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

    DrawBoxes Me

End Sub

In a standard module:-

Public Sub DrawBoxes(ByRef rpt As Report)
    Dim intMaxHeight As Integer
    Dim ctl As Control
    Dim lngColourLabel As Long, lngColourText As Long
    
    lngColourLabel = RGB(204, 255, 153)  'pale green
    lngColourText = RGB(236, 236, 236) 'pale grey
    
    'Find tallest control in Detail section
    For Each ctl In rpt.Section(0).Controls
        If ctl.Height > intMaxHeight Then
            intMaxHeight = ctl.Height
        End If
    Next
    
    rpt.DrawStyle = 6 'transparent border, solid fill
    
    'Draw a box around each control in Detail according to its tag property
    For Each ctl In rpt.Section(0).Controls
        If ctl.Tag = "label" Then
            rpt.Line (ctl.Left, ctl.Top)-Step(ctl.Width, intMaxHeight), lngColourLabel, BF
        End If
    
        If ctl.Tag = "text" Then
            rpt.Line (ctl.Left, ctl.Top)-Step(ctl.Width, intMaxHeight), lngColourText, BF
        End If
    Next

End Sub

Hope that helps.


Edit:
And yes, it should be the Detail_Print event.

Chris.