Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 4 of 4

Thread: How to set the comment size in VBA

  1. #1
    New Member
    Join Date
    Apr 2002
    Location
    Michigan, USA
    Posts
    17
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi,

    I used Record Macro to create a comment. The VBA code is as follow:

    Range("C1").Select
    Range("C1").AddComment
    Range("C1").Comment.Visible = False
    Range("C1").Comment.text text:="Test: A1 " & Chr(10) & "Result: 56"
    Selection.ShapeRange.ScaleWidth 5.87, msoFalse, msoScaleFromTopLeft
    Selection.ShapeRange.ScaleHeight 2.26, msoFalse, msoScaleFromTopLeft


    Once run Macro, get a error message:"Object doesn't support this property or method" for the statement "Selection.ShapeRange.ScaleWidth 5.87, msoFalse, msoScaleFromTopLeft
    Selection.ShapeRange.ScaleHeight 2.26, msoFalse, msoScaleFromTopLeft"


    Please help!! Big Thanks!!
    ssunchen

  2. #2
    BatCoder
    Join Date
    Feb 2002
    Location
    Turkey
    Posts
    813
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default

    Try this one: you are on the right way, i just used related Comment.Shape object to set scaleheight and width.

    With Range("C1")
    .Select
    .AddComment
    With .Comment
    .Visible = False
    .Text Text:="Test: A1 " & Chr(10) & "Result: 56"
    .Shape.ScaleHeight 2.26, msoFalse, msoScaleFromTopLeft
    .Shape.ScaleWidth 5.87, msoFalse, msoScaleFromTopLeft
    End With
    End With


    Regards

    [ This Message was edited by: smozgur on 2002-04-20 14:54 ]

    [ This Message was edited by: smozgur on 2002-04-20 14:54 ]

  3. #3
    MrExcel MVP Ivan F Moala's Avatar
    Join Date
    Feb 2002
    Location
    Auckland, New Zealand
    Posts
    4,209
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-04-20 12:36, ssunchen wrote:
    Hi,

    I used Record Macro to create a comment. The VBA code is as follow:

    Range("C1").Select
    Range("C1").AddComment
    Range("C1").Comment.Visible = False
    Range("C1").Comment.text text:="Test: A1 " & Chr(10) & "Result: 56"
    Selection.ShapeRange.ScaleWidth 5.87, msoFalse, msoScaleFromTopLeft
    Selection.ShapeRange.ScaleHeight 2.26, msoFalse, msoScaleFromTopLeft


    Once run Macro, get a error message:"Object doesn't support this property or method" for the statement "Selection.ShapeRange.ScaleWidth 5.87, msoFalse, msoScaleFromTopLeft
    Selection.ShapeRange.ScaleHeight 2.26, msoFalse, msoScaleFromTopLeft"


    Please help!! Big Thanks!!
    ssunchen
    You haven't got the comment selected...but
    then there is no need to select an object
    in order to change it.

    Try:


    Range("C1").Select
    Range("C1").AddComment
    Range("C1").Comment.Visible = False
    Range("C1").Comment.Text Text:="Test: A1 " & Chr(10) & "Result: 56"
    Range("C1").Comment.Shape.ScaleWidth 5.87, msoFalse, msoScaleFromTopLeft
    Range("C1").Comment.Shape.ScaleHeight 2.26, msoFalse, msoScaleFromTopLeft



    Or tidier;


    Sub Tidy()
    With Range("C1")
    .AddComment
    .Comment.Text Text:="Test: A1 " & Chr(10) & "Result: 56"
    .Comment.Shape.ScaleWidth 5.87, msoFalse, msoScaleFromTopLeft
    .Comment.Shape.ScaleHeight 2.26, msoFalse, msoScaleFromTopLeft
    End With
    End Sub



    Kind Regards,
    Ivan F Moala From the City of Sails

  4. #4
    Board Regular
    Join Date
    Feb 2002
    Posts
    3,184
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Comments eh! i hated them as BOARING in till Ivan F Moala showed me they ant gotta be i have sweet little blue and white clouds!

    What i found was the size was a pain and recorded them if regula size was required and so saved the agro and added the lines of code in, thus me being lazy.

    i sent Ivan these as thanks to him for the pointer, sad no excel i have so no code at disposal.. Thus why i rairly post my VBA
    Free Excel based Web Toolbar available here.

    Jack in the UK
    J & R Excel Solutions
    "making Excel work for you"

Some videos you may like

User Tag List

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
  •