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

[leonlai]

Hi,

I may just be dreaming...

If there are many comments on a worksheet, and we click a macro button that shows them all, some comments may hide others, so we cannot read them all.

My comments are programmed to autofit. So, they occupy minimum space. The button acts as a toggle button and can hide or show all comments.

I am just wondering if it is possible to "spread" the comments, so that one does not hide another one and we can read them all at a glance.

I googled, but don't know the keyword to search for, and so did not find an answer.

Any idea or url references will be much appreciated.

Thanks
Leon

 

[leonlai]

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

 

[My Aswer Is This]

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:

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

 

[leonlai]

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...

 

[leonlai]

Thanks again.

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

Leon

 

[My Aswer Is This]

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.
​

 

[leonlai]

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

 

[My Aswer Is This]

Thanks for that information

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

 

[My Aswer Is This]

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.

 

[leonlai]

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

 

[My Aswer Is This]

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.
​