Solved

Limit the Number of Values Displayed in Cell Comments

Posted on 2013-01-07
11
476 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
  • 6
  • 5
11 Comments
 
LVL 26

Accepted Solution

by:
redmondb earned 500 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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
 

Author Comment

by:Cook09
Comment Utility
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
Comment Utility
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
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 

Author Comment

by:Cook09
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Brian,
Thanks...this is indeed interesting.  I'll run the code now to clean up things.

Cook
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

772 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now