Auto-size Text in Report or Form - Possibly Lebans

[shane201980]

So, i've been doing a lot of searching and it seems that Access has never managed to fix an age old problem; shrinking text to fit. I know that I can use the Can grow/Can Shrink, but this will produce multiple lines and since this is for labels, I don't have lines to spare. Anyhow, I tried working with some code to check the length and then adjust the fontsize based on the length, but it seemed to adjust the font for all the labels. The next item I came across was some modules created by Lebans, which look like they can work for me, but I'm too novice at Access to figure out how to place these in my database. Here is the link to where i was able to download a copy, i'm hoping someone can help walk me through this. I know I can tell you a hundred things I tried, but they didn't work.



http://www.lebans.com/textwidth-height.htm

[June7]

Far as I can tell, that Lebans example does not shrink text, it merely sets control height for however many lines to be displayed. This might be useful on a form because CanGrow/CanSrink doesn't seem to even work on forms, does on reports. However, this will not be practical for forms set in Continuous or Datasheet views, only Single view, as demonstrated in the example. Changing a control property will be reflected in all instances of control because there is only one control, as seen on form in Design view.

Your idea of checking text length and setting fontsize to keep text length limited to a certain textbox width won't work well with proportional fonts. Maybe with non-proportional like Courier. Courier New in 12 point is about 10 characters per inch.

However, all instances of the form control (your label) on Datasheet or Continuous view will reflect this Fontsize property setting.

[shane201980]

June7,

You are absolutely correct, I posted the wrong link last night for an example on Leban's website; guess I was a little loopier than normal. Here is the one that will auto correct text:

http://www.lebans.com/autosizefont.htm

I'm going to take a fresh new look at this today, and see if I can get it working. But please, if anyone can give me some insight on this it is most definetly appreciated. I understand that this could still have the same effect as to control the Fontsize of all items of my report, please let me know if this is the case and i'll drop the subject. I can't believe that Access has continued to over look this, Microsoft has clearly had this feature for years in most other softwares.

Thanks!

[shane201980]

Ok, so I think I have a work around, but if anyone has any fresh ideas I'm game. This is what I'm currently using in my report and it does work, giving me variable font sizes depending on the text length. Of course this is not utilizing a non-proportional text like Courier, because this is something that needs to be presentable. i'm using Times New Roman, and I know that sooner or later I'll have something that doesn't fit properly(Trial and Error here). I placed this in the Detail Section of my report under Events, On Format:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
If Len([First Name]) > 15 Then
Me.txtFirst.FontSize = 24
Else
If Len([First Name]) > 11 Then
Me.txtFirst.FontSize = 28
Else
If Len([First Name]) > 10 Then
Me.txtFirst.FontSize = 36
Else
If Len([First Name]) > 9 Then
Me.txtFirst.FontSize = 40
Else
If Len([First Name]) > 8 Then
Me.txtFirst.FontSize = 42
Else
If Len([First Name]) > 0 Then
Me.txtFirst.FontSize = 48
End If
End If
End If
End If
End If
End If
End Sub

[June7]

Glad it is working. Consider Select Case structure instead of the If Then Else.

Select Case Len([First Name])
Case Is > 15
Me.txtFirst.FontSize = 24
Case Is > 11
...
End Select

[shane201980]

I never thought about that, so much cleaner too. Thanks!