Page 3 of 4 FirstFirst 1234 LastLast
Results 21 to 30 of 32

Thread: On a sheet with many comments, is it possible to spread them out so as not to hide other comments?

  1. #21
    Board Regular
    Join Date
    Mar 2019
    Location
    Republic of Mauritius
    Posts
    77
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: On a sheet with many comments, is it possible to spread them out so as not to hide other comments?

    Hi, My Aswer Is This

    I started this thread with:
    I may be dreaming...
    I am glad to tell you that you have made my dream come true.

    You have provided a solution I could not even dream of.
    My idea was to "spread out" all comments so that they can be easily read.
    I understand that this is going to be extremely difficult to achieve.

    Your solution is much more elegant and is extremely useful for me. It works like a charm!

    Thanks a lot
    Leon

  2. #22
    Board Regular My Aswer Is This's Avatar
    Join Date
    Jul 2014
    Posts
    15,820
    Post Thanks / Like
    Mentioned
    28 Post(s)
    Tagged
    13 Thread(s)

    Default Re: On a sheet with many comments, is it possible to spread them out so as not to hide other comments?

    Glad I was able to help you. You did not exactly say where you wanted the Textbox I provided displayed so I used active cell.

    If you need some modification then let me know.

    I would think just selecting the shape and pressing delete is easier then needing a Command Button to delete the shape.

    Thanks for that explanation.

    I did play around with the script I provided and made the statement:
    Select shape and press delete key smaller Text then other text and changed font color for that statement to White. If you think you need that you can try this modified script:
    Code:
    Sub Check_For_Comment()
    'Modified  3/30/2019  5:15:12 AM  EDT
    Dim r As Range
    Dim ans As String
    Dim ss As Long
    Dim DeleteMe As String
    Dim nn As Long
    DeleteMe = "To Delete Me:" & vbNewLine & "Select Me then  Press Delete Key"
    For Each r In Selection
        If Not r.Comment Is Nothing Then ans = ans & r.Comment.Text & vbNewLine
    Next
    If ans = "" Then MsgBox "No Comments found in selection": Exit Sub
    ans = ans & vbNewLine & DeleteMe
    ActiveSheet.Shapes.AddTextbox(msoTextOrientationHorizontal, 1, 1, 1, 1).Select
    With Selection.ShapeRange.TextFrame2
            .TextRange.Font.Size = 16
            .TextRange.ParagraphFormat.Alignment = msoAlignCenter
            .TextRange.Characters.Text = ans
            .TextRange.Font.Fill.ForeColor.RGB = RGB(0, 0, 0)
            .TextRange.Font.Bold = True
                ss = .TextRange.Characters.Count
                nn = ss - 47
          .TextRange.Characters(nn, 48).Font.Size = 14
        .TextRange.Characters(nn, 48).Font.Fill.ForeColor.RGB = RGB(255, 255, 255)
    End With
    With Selection
        .Left = ActiveCell.Left
        .Top = ActiveCell.Top
        .ShapeRange.Fill.ForeColor.RGB = RGB(0, 0, 255)
        .AutoSize = msoAutoSizeShapeToFitText
    End With
    End Sub
    Be sure and always test this script on sample data the first time you use this as to avoid any problems with your data. Always trying to learn more and help others where I can. I'm using Excel 2013.
    Patience please I知 not perfect yet. "Memories are forever"

  3. #23
    Board Regular
    Join Date
    Mar 2019
    Location
    Republic of Mauritius
    Posts
    77
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: On a sheet with many comments, is it possible to spread them out so as not to hide other comments?

    Hi, Yongle

    I have tried your suggestion and it works like a charm.

    By incorporating the ideas of My Aswer Is This, I have obtained the ideal macro for my project.

    Thanks a lot for all your help, and Best Regards,

    Leon

    closing...

  4. #24
    Board Regular
    Join Date
    Mar 2019
    Location
    Republic of Mauritius
    Posts
    77
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: On a sheet with many comments, is it possible to spread them out so as not to hide other comments?

    Thanks again.

    Great idea!
    I could never have thought it out by myself.

    Leon

  5. #25
    Board Regular My Aswer Is This's Avatar
    Join Date
    Jul 2014
    Posts
    15,820
    Post Thanks / Like
    Mentioned
    28 Post(s)
    Tagged
    13 Thread(s)

    Default Re: On a sheet with many comments, is it possible to spread them out so as not to hide other comments?



    You said:
    I am developing an Excel add-in using
    Add-In Express
    (this is a wonderful software!), and VB .NET for my company.

    I have never tried making a add-in
    Hope that works out for you.

    Glad I was able to help you.
    Come back here to Mr. Excel next time you need additional assistance.
    Be sure and always test this script on sample data the first time you use this as to avoid any problems with your data. Always trying to learn more and help others where I can. I'm using Excel 2013.
    Patience please I知 not perfect yet. "Memories are forever"

  6. #26
    Board Regular
    Join Date
    Mar 2019
    Location
    Republic of Mauritius
    Posts
    77
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: On a sheet with many comments, is it possible to spread them out so as not to hide other comments?

    Hi, My Aswer Is This

    I see you have a great experience of VBA.

    With Add-In Express, you can do a lot of things that VBA cannot do, like:

    > Customizing the Ribbon (i.e. placing your buttons on the Ribbon instead of on your worksheet, like in VBA).

    > Creating a Task Pane (which appears on your screen near your Excel Sheet). On the task pane, you can put whatever object (available in .NET Framework) you want, like radio buttons, check boxes, combo-boxes etc.

    But you will need: .NET Framework, Visual Studio and Add-in Express (or VSTO).

    You need also know how to program in C# or VB.NET. Luckily, VB .NET is very similar to VBA as both use the same Excel Object Model.

    Maybe, you may want to try! With the VBA experience you have, you can create great Excel add-ins!

    Best Regards,
    Leon
    Last edited by leonlai; Apr 1st, 2019 at 02:25 AM.

  7. #27
    Board Regular My Aswer Is This's Avatar
    Join Date
    Jul 2014
    Posts
    15,820
    Post Thanks / Like
    Mentioned
    28 Post(s)
    Tagged
    13 Thread(s)

    Default Re: On a sheet with many comments, is it possible to spread them out so as not to hide other comments?

    Thanks for that information


    Quote Originally Posted by leonlai View Post
    Hi, My Aswer Is This



    I see you have a great experience of VBA.

    With Add-In Express, you can do a lot of things that VBA cannot do, like:

    > Customizing the Ribbon (i.e. placing your buttons on the Ribbon instead of on your worksheet, like in VBA).

    > Creating a Task Pane (which appears on your screen near your Excel Sheet). On the task pane, you can put whatever object (available in .NET Framework) you want, like radio buttons, check boxes, combo-boxes etc.

    But you will need: .NET Framework, Visual Studio and Add-in Express (or VSTO).

    You need also know how to program in C# or VB.NET. Luckily, VB .NET is very similar to VBA as both use the same Excel Object Model.

    Maybe, you may want to try! With the VBA experience you have, you can create great Excel add-ins!

    Best Regards,
    Leon
    Last edited by My Aswer Is This; Apr 1st, 2019 at 03:00 AM.
    Be sure and always test this script on sample data the first time you use this as to avoid any problems with your data. Always trying to learn more and help others where I can. I'm using Excel 2013.
    Patience please I知 not perfect yet. "Memories are forever"

  8. #28
    Board Regular My Aswer Is This's Avatar
    Join Date
    Jul 2014
    Posts
    15,820
    Post Thanks / Like
    Mentioned
    28 Post(s)
    Tagged
    13 Thread(s)

    Default Re: On a sheet with many comments, is it possible to spread them out so as not to hide other comments?

    Thanks for that information. Sounds interesting.
    At the moment I do a lot with Excel UserForms. I can create a UserForm that can popup on the screen just using a shortcut key. Then I use a lot of Option Buttons since they have captions. I give the Option Button a Caption like Phonetic and a script will enter all the Values in active column with value Like Alpha Bravo Charlie. We call these Phonetic Alphabet. This is just a example.
    I have about 40 option buttons on my Userform each one for a different script have my UserForm stored in my Personal folder so it's available to be used on any Workbook.
    So that's how I run most of my scripts so I do not use the Ribbon to run most of my scripts.
    Not sure if you have ever used Excel UserForms.
    On my version of Excel 2013 I can have a Customized Ribbon where I can also run my scripts from if I want but on that ribbon you cannot have customize icons there are about 30 different icons which you must choose from.
    I'm glad to see you like using Excel. And I will look into what you have described. Wishing you a nice new week.
    Be sure and always test this script on sample data the first time you use this as to avoid any problems with your data. Always trying to learn more and help others where I can. I'm using Excel 2013.
    Patience please I知 not perfect yet. "Memories are forever"

  9. #29
    Board Regular
    Join Date
    Mar 2019
    Location
    Republic of Mauritius
    Posts
    77
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: On a sheet with many comments, is it possible to spread them out so as not to hide other comments?

    Oh yes!

    You can customize the Ribbon using xml, but it's a pain!
    There are also applications that customize the Ribbon (without requiring you to write a single XML code). I have tried them all. They work, but are not very reliable.

    With Add-in Express, you customize the ribbon within a few minutes!

    Another thing is that the VBA code is embedded in your Excel Workbook. For security reasons, this is bad, because there will always be a clever guy who will crack your code and discover sensitive information (like connection password to SQL Server).

    With VB .NET, this is not possible. All codes are in a dll.

    I'm glad to see you like using Excel
    Formerly, I used Crystal Reports for reporting purposes.
    I found that Excel can be a more versatile development platform. Also, users are more familiar with Excel.



    Regards
    Leon
    Last edited by leonlai; Apr 1st, 2019 at 03:28 AM.

  10. #30
    Board Regular My Aswer Is This's Avatar
    Join Date
    Jul 2014
    Posts
    15,820
    Post Thanks / Like
    Mentioned
    28 Post(s)
    Tagged
    13 Thread(s)

    Default Re: On a sheet with many comments, is it possible to spread them out so as not to hide other comments?

    You said:
    You
    can
    customize the Ribbon using xml, but it's a pain!

    Using Excel 2013 I can Right click on the Ribbon and choose Customize The Ribbon.
    Then it's very easy to customize the ribbon.
    Be sure and always test this script on sample data the first time you use this as to avoid any problems with your data. Always trying to learn more and help others where I can. I'm using Excel 2013.
    Patience please I知 not perfect yet. "Memories are forever"

Some videos you may like

User Tag List

Tags for this Thread

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
  •