Limit the Number of Values Displayed in Cell Comments

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

Thanks,

Cook

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.

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

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.

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

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.

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

Cook,

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

Regards,
Brian.

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

Select allOpen in new window

Regards,
Brian.

Brian,
Thanks...this is indeed interesting.  I'll run the code now to clean up things.

Cook