Solved

Limit the Number of Values Displayed in Cell Comments

Posted on 2013-01-07
11
483 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 500 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
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 

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

Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

Question has a verified solution.

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

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This article helps those who get the 0xc004d307 error when trying to rearm (reset the license) Office 2013 in a Virtual Desktop Infrastructure (VDI) and/or those trying to prep the master image for Microsoft Key Management (KMS) activation. (i.e.- C…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

729 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