CODEWITHSUNDEEP
vbaexcel
Holger
Holger

Reputation: 16

Scaling of font size with control size of a VBA user form control

I want to resize a VBA user form when it is displayed on a screen with different resolution. There does not seem to be a built in function for that. Therefore use some code which multiplies the Top, Left, Width, Height and Font.Size with the same factor, say 50%. However, this changes the appearance of the control: the text does not fit anymore in the area. This has nothing to do with the VBA code, because you get the same when you do the scaling by Hand:

I guess this is because the Control contains not only caption text but also the option button itself and some margins? Anyway my question is: How can I calculate the optimal scaling factor for the caption font.

Upvotes: 0

Views: 2580

Answers (1)

Steven Martin
Steven Martin

Reputation: 3272

Thats not gonna be easy to fix using Tahoma so don't use it, use a mono-spaced font instead.

Find the width of a CHAR as a ratio of its pt (6,7,8,10,12,14,16 etc)

Ratio seems to be about 1.8 for Dejavu Sans Mono

then use 

(width of button - margins)  / len(text) = width of letter
 width of letter * ptToFontRatio = pt size

Button 120px wide with 13 chars as above

(120-9)/13 = 8.53
 8.53 * 1.8=  15.3  = closest smaller pt = 14pt

Button 60px wide with 13 chars as above

(60-9)/13 = 3.92
3.92 * 1.8 = 7.056  - closest smaller pt =  7pt

Note: its always gonna be approx when working these out so round things down to suit

Upvotes: 1

Related Questions