Results 1 to 7 of 7

Thread: changing font size depending on length of string
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    May 2014
    Posts
    37
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default changing font size depending on length of string

    Hi

    I need to complete a form. one of the fields has a variable length. I need to have the font size reduced if the length of the string is more than 20.

    for a len of less than 20 I can use a 12 Pt Font but for more than 20 a 10 Pt font would fit.

    I have tried to do this myself but it always goes bananas

    Any help would be appreciated.

  2. #2
    MrExcel MVP
    Moderator
    RoryA's Avatar
    Join Date
    May 2008
    Location
    UK
    Posts
    33,986
    Post Thanks / Like
    Mentioned
    52 Post(s)
    Tagged
    6 Thread(s)

    Default Re: changing font size depending on length of string

    When you say a form, do you mean a userform?

  3. #3
    New Member
    Join Date
    May 2014
    Posts
    37
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: changing font size depending on length of string

    No but I am not sure what a userform is.

    I take data from our ERP system and produce certificates, such as Certificate of conformity or of Origin. It is typed into a Add data worksheet and then the data is taken to the Certificate sheet. All the fields except the one is question are the same size but the Customer Order No field if of a variable length. If it is over 20 characters it is larger then the cell and doesn't print the full number hence I wish to make it a smaller font to fit.

    I tried an If statement on the LEN

    similar to

    if LEN(range("a32) > 20
    large font
    else
    small font
    End if

    it's the large/small font I don't seem to be able to get to work.

  4. #4
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    29,106
    Post Thanks / Like
    Mentioned
    483 Post(s)
    Tagged
    48 Thread(s)

    Default Re: changing font size depending on length of string

    How about
    Code:
    Sub FontSize()
       Range("A32").Font.size = IIf(Len(Range("A32")) > 20, 10, 12)
    End Sub
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  5. #5
    MrExcel MVP
    Moderator
    RoryA's Avatar
    Join Date
    May 2008
    Location
    UK
    Posts
    33,986
    Post Thanks / Like
    Mentioned
    52 Post(s)
    Tagged
    6 Thread(s)

    Default Re: changing font size depending on length of string

    If it's just a cell on a worksheet, you could set the Shrink to Fit option on the Alignment tab of the Format Cells dialog.

  6. #6
    New Member
    Join Date
    May 2014
    Posts
    37
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: changing font size depending on length of string

    Hi Fluff

    That's great and so simple.

    Thanks

  7. #7
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    29,106
    Post Thanks / Like
    Mentioned
    483 Post(s)
    Tagged
    48 Thread(s)

    Default Re: changing font size depending on length of string

    Glad we could help & thanks for the feedback
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •