Results 1 to 9 of 9
  1. #1
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Does tring fit in cell? (Excel2003 VBA)

    Your method is probably as good as any. (You can also use the Shrink to Fit property)

  2. #2
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    Terneuzen, Netherlands
    Posts
    895
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Does tring fit in cell? (Excel2003 VBA)

    This works almost, for texts that are long the width reset is not OK yet (Columns(.Column).Width = orgWidth). Can't see quickly what's wrong... any idea?

    <pre>Sub Get_Text_Width()
    Dim orgWidth As Long, orgText As String, orgWrap As Boolean, orgShrink As Boolean
    Dim newText As String, newWidth As Long
    '
    With Selection

    'Get status
    orgWidth = .Width
    orgText = .Value
    orgWrap = .WrapText
    orgShrink = .ShrinkToFit

    'Test new
    newText = InputBox("Give new text", , orgText)
    .WrapText = False
    .ShrinkToFit = False
    .Value = newText
    Columns(.Column).EntireColumn.AutoFit
    newWidth = .Width

    'Restore
    .WrapText = orgWrap
    .ShrinkToFit = orgShrink
    .Value = orgText
    Columns(.Column).Width = orgWidth

    End With
    '
    MsgBox "Original width: " & orgWidth & " Testwidth: " & newWidth
    End Sub
    </pre>


  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Does tring fit in cell? (Excel2003 VBA)

    The Width property of a column is read-only, so this code won't work at all: the line

    Columns(.Column).Width = orgWidth

    will cause an error. You should use ColumnWidth instead of Width, since that is a read/write property.

  4. #4
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    Terneuzen, Netherlands
    Posts
    895
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Does tring fit in cell? (Excel2003 VBA)

    I used that originally but got an error, then I changed to "Width"; in other words: ColumnWidth also didn't work...

  5. #5
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    Terneuzen, Netherlands
    Posts
    895
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Does string fit in cell? (Excel2003 VBA)

    <P ID="edit" class=small>(Edited by StuartR on 21-May-08 17:10. Corrected spelling error in title to help future searches)</P>I have some text (maybe a few words; NOT Courier font) that needs to go into a cell (certain font, certain point size, certain type, e.g. bold). The cell has a width that I do not want to change. I do not want the text inside the cell to wrap (as either parts with be not readable or the cell height will adjust -and I also don't want that).

    How can I find out if the text fits in the cell? I don't think I can just add the wisth of individual characters in my text (ever if I knew how to do that in the first place) as that wouldn't take care of kerning.

    I can think of a workaround (elegant I guess, but somewhat crude): get the current cell width & contents, put the new target text in, autosize, get the new width. Then take the text back out and restore the cell contents and width.

    Any better ideas?

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Does tring fit in cell? (Excel2003 VBA)

    You should declare the width variables as Single, not as Long. It works OK when I test it.

  7. #7
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    Terneuzen, Netherlands
    Posts
    895
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Does tring fit in cell? (Excel2003 VBA)

    Yeah... tried that too. The units returned by columnwidth are different from those returned by the width statement. If quickly tried to find a convert function but was not (yet) successful.

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Does tring fit in cell? (Excel2003 VBA)

    There is no really good conversion between Width and ColumnWidth. Why isn't ColumnWidth good enough?

  9. #9
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    Terneuzen, Netherlands
    Posts
    895
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Does tring fit in cell? (Excel2003 VBA)

    OK, got it... here's a working end-result:

    <pre>Sub Get_Text_Width()
    Dim orgWidth As Single, orgText As String, orgWrap As Boolean, orgShrink As Boolean
    Dim newText As String, newWidth As Single
    '
    With Selection
    'Get status
    orgWidth = Columns(.Column).ColumnWidth
    orgText = .Value
    orgWrap = .WrapText
    orgShrink = .ShrinkToFit
    'Test new
    newText = InputBox("Give new text", , orgText)
    .WrapText = False
    .ShrinkToFit = False
    .Value = newText
    Columns(.Column).EntireColumn.AutoFit
    newWidth = Columns(.Column).ColumnWidth
    'Restore
    .WrapText = orgWrap
    .ShrinkToFit = orgShrink
    .Value = orgText
    Columns(.Column).ColumnWidth = orgWidth
    End With
    '
    MsgBox "Original width: " & orgWidth & " Testwidth: " & newWidth
    End Sub
    </pre>


Posting Permissions

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