Results 1 to 3 of 3
  1. #1
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    auto adjust comments (Excel 2003)

    I have code that automatically adds a comment to a cell with data about a neighoring table. This works fine but I now see that as I add text to the comment, if the person views the comment, it doesn't auto-size to let them view all the text. How can I fix this? ('rng' is Range of a specific cell, 'txtComment' is String)
    <pre> If coDat.Count Then
    For Each dat In coDat
    txtComment = txtComment & "[" & dat.custTheater & "] " & _
    dat.custName & Chr(10)
    Next dat
    End If
    </pre>

    This the code that populates the comment. The text comes from a collection of custom data types (made from a class module). I recorded a macro to see what code is used to expand the help and got this:
    <pre> Selection.ShapeRange.ScaleHeight 1.99, msoFalse, msoScaleFromTopLeft</pre>

    After seeing this, it seems I could add some factor for each element in the collection (like 0.50) for the .ScaleHeight but does that change based on the user's resolution?

    Example: If coDat.count = 10 (for 10 records to be written on their own line in the comment), I could do 10 * 0.50 (for the scaling factor) after the FOR loop

    Other ideas?

    Thnx,
    Deb

  2. #2
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: auto adjust comments (Excel 2003)

    SOLUTION FOUND: Here's my code, I experimented with the scaling factor and picked a multiplier for each line of text I wanted to add to the comment. I still wonder if it's resolution-dependent.
    <pre>Private Sub addComment(rng As Range, coDat As Collection)
    Dim k As Integer, dat As custData
    Dim txtComment As String, size As Single

    On Error Resume Next ' delete existing comment if any
    rng.Comment.Delete
    On Error GoTo 0

    If coDat.Count Then
    For Each dat In coDat
    txtComment = txtComment & "[" & dat.custTheater & "] " & _
    dat.custName & Chr(10)
    Next dat
    End If

    size = 0.25 + (coDat.Count * 0.25)

    rng.addComment
    rng.Comment.Text Text:=txtComment
    rng.Comment.Shape.ScaleHeight size, 0, 0

    End Sub</pre>


    Deb

  3. #3
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: auto adjust comments (Excel 2003)

    > I still wonder if it's resolution-dependent.

    I have used a scaling factor ("multiplier") to cope with text size on a GUI form. My solution ended up as a hit-and-miss affair. No matter what units I used, or how I discussed this with the controls, it wasn't perfect. In the end I aimed at using 95% of the potential space, and was happy enough at that. There's probably an API call I'm unaware of that matches the problem.

Posting Permissions

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