Adjusting textbox font size based on string length

[mzza]

Hi there,

as per the thread title, I am trying to adjust the font size of the text in an embedded textbox in the "Quote" sheet, using the following code:

Set wordobj2 = Sheets("Quote").OLEObjects(2)
wordobj2.Object.Text = Countries_String

    Select Case CountryCharLength
        Case 1 - 1400
            wordobj2.Object.Font.Size = 12
        Case 1401 - 2000
            wordobj2.Object.Font.Size = 11
        Case 2001 - 2500
            wordobj2.Object.Font.Size = 10
        Case 2501 - 3000
            wordobj2.Object.Font.Size = 9
        Case 3001 - 3500
            wordobj2.Object.Font.Size = 8
        Case 3501 - 4000
            wordobj2.Object.Font.Size = 7
        Case Is > 4000
            wordobj2.Object.Font.Size = 6
     End Select

"CountryCharLength" is an integer based on the character length of the string I'm passing into the textbox, and is evaluating correctly. Why does my Select Case statement not change the font size? Like, ever? :s

On a related note - can I refer to my textbox by the name I've given it ("TxtBoxCountries") in its Properties? What's the correct syntax for that?

TIA

 

[diddi]

try using...

Case 1 To 1400

etc

 

[mzza]

I believe this is what they call a 'schoolboy error'... >.<

Thank you diddi, much appreciated

 

[diddi]

easy to do. btw if your font is proportional, you may get odd results. i seem to remember theire is a Width() of a string which gives actual pixel width.