Solved

Access VBA  - automatically fit size of label to text

Posted on 2010-08-14
10
1,228 Views
Last Modified: 2012-05-10
Hi

I ahve a report where labels are automatically populated with data and centred. My only problem is
that sometimes the text is too long for the label so a portion of it gets hidden. What VBA code would I use to automatically size the label to fit whatever text is in it.

Thanks
0
Comment
Question by:Murray Brown
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 5
10 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 33436730
have you tried setting the property Can Grow and Can Shrink to Yes?
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 33436762
0
 

Author Comment

by:Murray Brown
ID: 33436774
Hi

I can't ssem to find that property in my label. Pity they aren't in alphebetical order
0
Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

 

Author Comment

by:Murray Brown
ID: 33436785
looked at that link and can't see what you mean
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 33436812
download the file TextHeightWidth.zip from the link and see if you can apply the method used in the sampledb
0
 

Author Comment

by:Murray Brown
ID: 33437026
That example is pretty complex. Surely Microsoft have a simpler way to do something so obviously necessary!
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 33437092
here is what you can do..

check the length of the caption (how many characters will fit in the Label using the default font size)
then do this

assuming the max number of characters that will fit using the default font that you have is 25

select case Len(me.labelx.caption)
      case 26-28
           me.labelx.fontsize=10


end select

something like that


can you upload your db...




   



end select
0
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 500 total points
ID: 33437126
here is a function you can use place this codes in regular module





Function ResizeFont(MyStr As String) As Integer
Dim intLen As Integer

intLen = Len(MyStr)
Select Case intLen
Case Is < 10: ResizeFont = 18
Case Is < 20: ResizeFont = 16
Case Is < 30: ResizeFont = 16
Case Is < 40: ResizeFont = 14
Case Is < 50: ResizeFont = 12
Case Is < 60: ResizeFont = 10
Case Is < 70: ResizeFont = 8
Case Else: ResizeFont = 6
End Select
End Function

Open in new window

0
 

Author Closing Comment

by:Murray Brown
ID: 33437197
Thanks very much
0
 

Author Comment

by:Murray Brown
ID: 33437652
To anyone who reads this post: I found a far more workable solution to my initial question. I stretched the label manually to the width of the report and then set the TextAlign property to "Centre"
0

Featured Post

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

688 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question