?
Solved

Limit the Number of Values Displayed in Cell Comments

Posted on 2013-01-07
11
Medium Priority
?
484 Views
Last Modified: 2013-01-11
Currently, when the attached code is run, it displays about 12-15 significant digits, within a Cell Comment, unless a Round is used to limit the sigificant digits, after a decimal.  However, with a workbook that has thousands of forumlas, is there a way, within VBA to limit the Comment Display to six places after the decimal?

Tried to limit it with the the cell.NumberFormat , but that doesn't seem to work, the comment is Blank if anything else is added to it.

Sub CommentThem()
  Dim cell As Range
  Dim cmt As Comment
  
  On Error Resume Next
  Selection.ClearComments

  On Error GoTo 0
  For Each cell In Intersect(Selection, ActiveSheet.UsedRange)
     If cell.Formula <> "" Then
        cell.AddComment
        cell.Comment.Visible = False
        cell.Comment.Shape.AutoShapeType = msoShapeFlowchartAlternateProcess
        cell.Comment.Shape.Fill.ForeColor.RGB = RGB(242, 242, 242)
        On Error Resume Next  'fails on invalid formula
        cell.Comment.Text Text:=cell.Address(0, 0) & _
           "  Value:    " & cell.Value & chr(10) & _
           "  Formula:  " & cell.Formula & chr(10) & _
           "  Format:   " & cell.NumberFormat            '= '"General" ' "0.000000"
        cell.Comment.Shape.ScaleWidth 1.25, msoFalse, msoScaleFromTopLeft
        cell.Comment.Shape.ScaleHeight 0.69, msoFalse, msoScaleFromTopLeft
       On Error GoTo CodeError

        With cell.Comment.Shape.TextFrame.Characters.Font
            .Name = "Calibri"
            .Size = 9
            .Color = RGB(0, 75, 145)    '-7255296
            .Bold = True
        End With

        On Error GoTo 0
     End If
  Next cell

CodeError:
 ' MsgBox "Error with Comment Format"
End Sub

Open in new window

0
Comment
Question by:Cook09
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 5
11 Comments
 
LVL 26

Accepted Solution

by:
redmondb earned 2000 total points
ID: 38752456
Hi, Cook09.

I'm not sure that I've correctly understood you, but if you want to limit the value you're writing to the cell to six places of decimal then replace line 17 by...
"  Value:    " & Format(cell.Value, "#,##0.######;-0;#,##0.######;general") & Chr(10) & _

Regards,
Brian.
0
 

Author Closing Comment

by:Cook09
ID: 38752764
Brian,
I believe you understood correctly, and it worked like exactly as needed on the few that I tried.

Thanks,

Cook
0
 
LVL 26

Expert Comment

by:redmondb
ID: 38752963
Thanks, Cook.

I keep an on "my" closed questions for a number of weeks so if you have any issues please feel free to post here.

Regards,
Brian.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

Author Comment

by:Cook09
ID: 38754781
Brian,
I appreciate it, and yes I do have two minor questions.  Being involved with Statistics, sometimes spreadsheets are available to download that show a particular feature or function.
In a couple of cases, I don't like the formatting and want to change it.  For instance, some numbers I want Centered, or the fill, a different color.  When I manually try to do so, it won't let me.  I've checked to see if there is any code and there is not.  Are they doing something with conditional formatting, or another method to keep cells from being re-formatted?  There doesn't seem to be any protection, so it's puzzling.

The second question is the most puzzling.  When a worksheet is copied to another workbook, a number of hidden names are created, that begin with _xfn.  Theses I have found with Name Manager.  It's not a big deal, as they can be deleted.   However, some code somewhere is creating them.  How would I begin to investigate?

Appreciate your willingness to assist.

Cook
0
 
LVL 26

Expert Comment

by:redmondb
ID: 38757616
Cook,

When I manually try to do so, it won't let me.
Without seeing examples, all I can think is that the formatting you're seeing is actually Conditional Formatting. I doubt they're trying to stop you changing the formatting (you could just update the CF), it's just that CF has precedence of normal formatting.

a number of hidden names are created, that begin with _xfn.
A few weeks ago I came across a funny with Names beginning with _xlnm. - they're internal Names which are never shown in the GUI in that form. However, I can't find anything on _xfn.
I'd love a copy of a file containing these entries (and the file it came from). Could you redact one of your files, create the copy with the funny Names and post both here, please?

Many thanks,
Brian.
0
 

Author Comment

by:Cook09
ID: 38760899
Brian,
Attached is one that I created, labor intensive, but from the Microsoft Web Site.  Although, the Names have been removed once, if I'm not mistaken. But, it makes sense about being an internal name, as it has occured very frequently, and with internal names that weren't part of the moving/copying.  

I'm sure you're right about the CF, that's my next task.

Thanks,

Cook
Formula-Functions-Abridged.xlsm
0
 
LVL 26

Expert Comment

by:redmondb
ID: 38761363
Thanks, Cook.

However, I can't find anything on _xfn.
Oh dear, spot the misspelling.

However, _xlfn. may just be a red herring - it's simply a placeholder for a function that's not supported in the version of Excel being used. Here's a MS page on it.

I'm still suspicious about the integrity of the file...
(1) When I first opened the file I got a request to fix it. I said yes, but didn't save it. The next time I opened it I didn't get the message. So, I must have saved it, right? Nope, I downloaded the file a second time and that one was identical to the original.
(2) There are three visible Names. One of them ("Functions_List") is a bit peculiar...
 - I can't edit it.
 - I ran a macro to list all Names, hidden and visible. It doesn't appear.
(3) There are 62 Hidden Names. Most of them have "#REF!" or "#NAME?" errors.
(4) 13 of the Hidden names start with _xlfn. - they all have "#NAME?" errors.

What versions of Excel did the file pass through?

Thanks,
Brian.
0
 

Author Comment

by:Cook09
ID: 38763474
Brian,
Attached is a workbook that has the original names, which I need to clean up, from the original Formulas-Functions workbook that has over 400 worksheets.  The second tab contains the names from the original Abridged version. The third tab are names from the downloaded file, from EE, as you did. It is interesting.  

Quite a few names, from the Original File, seem to come from a Functions List Worksheet. I've included that worksheet as well.

I believe that all #REF and others should be deleted, but if they are placeholders, what's their function and/or do they serve a purpose? What are your thoughts?

I also checked the CF, and you were correct.  Simple Fix.

Cheers,
Cook
Worksheet-Names.xlsx
0
 
LVL 26

Expert Comment

by:redmondb
ID: 38765628
Cook,

Sorry, I just saw this now and it's past my bedtime. I'll get back to you tomorrow.

Regards,
Brian.
0
 
LVL 26

Expert Comment

by:redmondb
ID: 38766720
Cook,

I feel the same way about a file that shows any sort of corruption as I do about a disk that starts to make funny noises - it's usually only a matter of time before there are tears. That being said, doing a complete "text-only" rebuild is usually very labour-intensive, so you'll probably want to try a repair first.

(1) The macro below lists all Names and deletes any of them with errors in their RefersTo. Surprise, surprise - it doesn't handle the "_xlfn." Names nor Functions_List (which it doesn't even see).
(2) Get rid of the Functions_List Name by rebuilding the Functions-List sheet. (Deleting that sheet drops the Name.)
(3) I don't know what to say about the _xlfn. Names. As a matter eof interest, I found one in my Personal.xlsb.

The macro is...
Sub Delete_All_Names_with_RefersTo_Errors()
Dim xRow As Long
Dim xName As name
Dim xResponse As Long

xResponse = MsgBox("About to delete all Names whose RefersTo contains an error from """ & ActiveWorkbook.name & """." _
            & Chr(10) & "('OK' to continue, 'Cancel' to quit.)", vbOKCancel, "Delete_All_Names_with_RefersTo_Errors")
If xResponse = 2 Then
    MsgBox "User chose not to continue. Run terminated."
    Exit Sub
End If

Sheets.Add

Range("A1:L1") = Array("No.", "Name", "RefersTo", "Comment", "Macro Type", "Category", "RefersTo Range", "Short-Cut Key", "Valid Book Parameter", "Value", "Visible", "Deleted?")
xRow = 2

Application.ScreenUpdating = False
    
    For Each xName In ActiveWorkbook.Names
        
        With xName
        
            Cells(xRow, 1) = .Index
            Cells(xRow, 2) = .name
            Cells(xRow, 3) = " " & .RefersTo
            Cells(xRow, 4) = " " & .Comment
            Cells(xRow, 5) = " " & .MacroType
            
            Application.DisplayAlerts = False
            On Error Resume Next
                Cells(xRow, 6) = " " & .Category
                Cells(xRow, 7) = " " & .RefersToRange
                Cells(xRow, 8) = " " & .ShortcutKey
            On Error GoTo 0
            Application.DisplayAlerts = True
            
            Cells(xRow, 9) = " " & .ValidWorkbookParameter
            Cells(xRow, 10) = " " & .Value
            Cells(xRow, 11) = " " & .Visible
            If InStr(1, .RefersTo, "#") > 0 Then
                On Error Resume Next
                    ActiveWorkbook.Names(.Index).Delete
                    If Err.Number <> 0 Then
                        Debug.Print .Index & " - " & .name & " - Error # " & CStr(Err.Number) & " " & Err.Description & " " & Err.Source
                        Cells(xRow, 12) = "Error - " & Err.Number
                        Err.Clear
                    Else
                        Cells(xRow, 12) = "Y"
                    End If
                On Error GoTo 0
            End If
                
            xRow = xRow + 1
    
        End With
    
    Next xName
    
Application.ScreenUpdating = True

End Sub

Open in new window

Regards,
Brian.
0
 

Author Comment

by:Cook09
ID: 38767376
Brian,
Thanks...this is indeed interesting.  I'll run the code now to clean up things.

Cook
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes how to import an Outlook PST file to Office 365 using a third party product to avoid Microsoft's Azure command line tool, saving you time.
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question