Select and change font size of words by number of characters

knpaddac · #1 03-15-2021, 05:47 AM

Is there some way to create a macro that would size all words in a document based on the number of letters, numbers, non-spaces in the string?

I have to make adhesive labels for book spines and typically anything with 9 or more characters needs to be sized to 10 points to fit on the labels.

Charles Kenyon · #2 03-15-2021, 08:29 AM

[Moderator's note: I edited your post to use the word "select" instead of "highlight" since I am confident that is what you meant. I also specified that what you want to do is size rather than just select the text. If I was mistaken, please change it back. Feel free to lambast me.]

Yes. It is possible. Anyone trying to help you with this is going to want to know how you are preparing the labels.

Mail Merge? (This is most efficient.)

How many labels at a time?

Can you provide a sample document? How to attach a screenshot or file in this forum. (Word file, not screenshot)

How familiar are you with writing macros in the vba editor?

knpaddac · #3 03-15-2021, 09:24 AM

To clarify, this if for books in a library, so the process goes something like this:

-the books are cataloged in the ILS
-in the ILS I pull lists of the books to be labeled and then the call numbers are exported to a csv file in Excel. Ideally i do 100 at a time, as that is how many are on each sheet (8.5x11) but that is rarely the case, it varies...40, 50, 72, etc.
-in Excel, I manually shift the cells into columns of 10 each and then copy/paste that onto the dummy template Word file from the company that produces the labels. These are .75x1 inch labels, 100 per sheet.
-from there I highlight all the labels and use a macro I recorded which replaces all spaces with linebreaks (or maybe paragraphs is the correct term), changes the font to Arial rounded MT Bold, and sets the size to 11pt.
-THIS IS THE PART I AM LOOKING TO CUTDOWN ON: At this point I have to manually go through and modify any instances where the names or other info is too long to fit on a line, downsizing such lines to 10pt, 9pt, or whatever. Typically a string of 8 letters seems to fit, but more than that tends to throw off the formatting. So, if there were some way to automatically search for strings of 9 or more and downsize them to 10pt, I think that would be beneficial.

I have attached a couple of documents, the '.75x1' is the mostly blank template I paste the info into and the other is a recent sheet as it ended up being printed.

Quote:

Originally Posted by Charles Kenyon

[Moderator's note: I edited your post to use the word "select" instead of "highlight" since I am confident that is what you meant. I also specified that what you want to do is size rather than just select the text. If I was mistaken, please change it back. Feel free to lambast me.]

Yes. It is possible. Anyone trying to help you with this is going to want to know how you are preparing the labels.

Mail Merge? (This is most efficient.)

How many labels at a time?

Can you provide a sample document? How to attach a screenshot or file in this forum. (Word file, not screenshot)

How familiar are you with writing macros in the vba editor?

macropod · #4 03-15-2021, 03:00 PM

Such labels are usually generated via a mailmerge, in which case, see: https://www.msofficeforums.com/125792-post2.html

Otherwise, try the following macro:

Code:

Sub Demo()
Application.ScreenUpdating = False
Dim Tbl As Table, Cll As Cell, Par As Paragraph, sCllWdth As Single, sParWdth As Single
With ActiveDocument
  For Each Tbl In .Tables
    With Tbl
      With .Cell(1, 1)
        sCllWdth = .Width - .LeftPadding - .RightPadding
      End With
      For Each Cll In .Range.Cells
        With Cll
          .WordWrap = False
          If Len(.Range) > 2 Then
            For Each Par In .Range.Paragraphs
              With Par.Range
                sParWdth = .Characters.Last.Previous.Information(wdHorizontalPositionRelativeToPage)
                sParWdth = sParWdth - .Characters.First.Information(wdHorizontalPositionRelativeToPage)
                If sParWdth + Par.LeftIndent > sCllWdth Then .FitTextWidth = sCllWdth - Par.LeftIndent
                If .Characters.Last.Previous.Information(wdVerticalPositionRelativeToPage) <> _
                  .Characters.First.Information(wdVerticalPositionRelativeToPage) Then
                  .FitTextWidth = sCllWdth - Par.LeftIndent
                End If
              End With
            Next
          End If
        End With
      Next
    End With
  Next
End With
Application.ScreenUpdating = True
End Sub

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
VBA - Word how to globally change the font and font size in footnotes	thomasoj	Word VBA	3	01-15-2020 06:26 AM
VBA - change font and font size for Word footnote	thomasoj	Word VBA	1	01-15-2020 02:37 AM
Looping Macro to Change Font, Font Size, and Give Heading 1	WH7262	Word VBA	1	08-26-2014 03:46 PM
how change size font to inches size	kkepo	Word	4	08-28-2012 08:53 PM
Highlight the first X number of characters	14spar15	Word	1	11-13-2011 11:17 PM