Results 1 to 9 of 9
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Too Many different cell formats (2000)

    I have an Excel spreadsheet that I have copied 16 different spreadsheets to 16 tabs in this spreadsheet. Everything was working fine but now it gives me an error when I try to open the spreadsheet. The error is - "Too many different cell formats". Is there a way to get around the error and open this spreadsheet and maybe salvage some of the 16 tabs?

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Too Many different cell formats (2000)

    Maybe this message gives you some idea's?
    If you cannot get the file open, maybe you can send it to me and I will have a go at it.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Too Many different cell formats (2000)

    Can you still open the workbook after the error message? If so, see here for a macro that deletes unused number formats.

  4. #4
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Too Many different cell formats (2000)

    The spreadsheet does not open after the error. I tried some of the links in the other post and they said page not found. Is it possible to recover?

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Too Many different cell formats (2000)

    Contact Pieterse in a PM, he will let you know where you can send the workbook for repairing.

  6. #6
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Too Many different cell formats (2000)

    Hi Hans:

    I got the file to open by downloading OpenOffice.org software. I wanted to use the macro you suggested but it is giving me errors: The macro is a follows:
    Sub DeleteUnusedCustomNumberFormats()
    'leo.heu...@get2net.dk, May 6. 2001
    'Version 1.01
    Dim Buffer As Object
    Dim Sh As Object
    Dim SaveFormat As Variant
    Dim fFormat As Variant
    Dim nFormat() As Variant
    Dim xFormat As Long
    Dim Counter As Long
    Dim Counter1 As Long
    Dim Counter2 As Long
    Dim StartRow As Long
    Dim EndRow As Long
    Dim pPresent As Boolean
    Dim NumberOfFormats As Long
    Dim Answer
    Dim Cell As Object
    Dim DataStart As Long
    Dim DataEnd As Long
    Dim AnswerText As String
    Dim ActWorkbookName As String
    Dim BufferWorkbookName As String
    NumberOfFormats = 1000
    StartRow = 3 ' Do not alter this value
    EndRow = 16384 ' For Excel 97 and 2000 set EndRow to 65536
    ReDim nFormat(0 To NumberOfFormats)
    AnswerText = "Do you want to delete unused custom formats from the"
    workbook?"
    AnswerText = AnswerText & Chr(10) & "To get a list of used and unused"
    formats only, choose No."
    Answer = MsgBox(AnswerText, 259)
    If Answer = vbCancel Then GoTo Finito
    On Error GoTo Finito
    ActWorkbookName = ActiveWorkbook.Name
    Workbooks.Add
    BufferWorkbookName = ActiveWorkbook.Name
    Set Buffer = Workbooks(BufferWorkbookName).ActiveSheet.Range("A 3")
    nFormat(0) = Buffer.NumberFormatLocal
    Buffer.NumberFormat = "@"
    Buffer.Value = nFormat(0)
    Workbooks(ActWorkbookName).Activate
    Counter = 1
    Do
    SaveFormat = Buffer.Value
    DoEvents
    SendKeys "{TAB 3}"
    For Counter1 = 1 To Counter
    SendKeys "{DOWN}"
    Next Counter1
    SendKeys "+{TAB}{HOME}'{HOME}+{END}^C{TAB 4}{ENTER}"
    Application.Dialogs(xlDialogFormatNumber).Show nFormat(0)
    ActiveSheet.Paste Destination:=Buffer
    Buffer.Value = Mid(Buffer.Value, 2)
    nFormat(Counter) = Buffer.Value
    Counter = Counter + 1
    Loop Until nFormat(Counter - 1) = SaveFormat
    ReDim Preserve nFormat(0 To Counter - 2)
    Workbooks(BufferWorkbookName).Activate
    Range("A1").Value = "Custom formats"
    Range("B1").Value = "Formats used in workbook"
    Range("C1").Value = "Formats not used"
    Range("A1:C1").Font.Bold = True
    For Counter = 0 To UBound(nFormat)
    Cells(StartRow, 1).Offset(Counter, 0).NumberFormatLocal =
    nFormat (Counter)
    Cells(StartRow, 1).Offset(Counter, 0).Value = nFormat(Counter)
    Next Counter
    Counter = 0
    For Each Sh In Workbooks(ActWorkbookName).Worksheets
    For Each Cell In Sh.UsedRange.Cells
    fFormat = Cell.NumberFormatLocal
    If Application.WorksheetFunction.CountIf(Range(Cells( StartRow,
    2), Cells(EndRow, 2)), fFormat) = 0 Then
    Cells(StartRow, 2).Offset(Counter, 0).NumberFormatLocal =
    fFormat
    Cells(StartRow, 2).Offset(Counter, 0).Value = fFormat
    Counter = Counter + 1
    End If
    Next Cell
    Next Sh
    xFormat = Range(Cells(StartRow, 2), Cells(EndRow, 2)).Find("").Row - 2
    Counter2 = 0
    For Counter = 0 To UBound(nFormat)
    pPresent = False
    For Counter1 = 1 To xFormat
    If nFormat(Counter) = Cells(StartRow, 2).Offset(Counter1,
    0).NumberFormatLocal Then
    pPresent = True
    End If
    Next Counter1
    If pPresent = False Then
    Cells(StartRow, 3).Offset(Counter2, 0).NumberFormatLocal =
    nFormat (Counter)
    Cells(StartRow, 3).Offset(Counter2, 0).Value = nFormat(Counter)
    Counter2 = Counter2 + 1
    End If

    What is missing?

    When I run it, it gives me a compile error - For without Next?

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Too Many different cell formats (2000)

    Hmm, the code in that newsgroup post is incomplete. Here is a link to an older but complete version. Scroll down to the contribution by Leo Heuser.

  8. #8
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Too Many different cell formats (2000)

    Thanks Hans - I am done for the day so you probably won't here from me until next year.........

  9. #9
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Too Many different cell formats (2000)

    <hr>I got the file to open by downloading OpenOffice.org software.<hr>
    Which is exactly what I would have used.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •