Too many different cell formats!

[serdar]

This error occcurs when i add some worksheets to a workbook from another
one. I am not completely sure (cos this is not my work actually) but it
seems to me that there is not really too much (about 4000?) "different" cell
formats in the workbook, but there is a quite lot amount of drawing objects
(grouped technical drwaings plus autocad objects which i also converted them
to bitmaps to overcome the error).

I also dont understand the restriction:
If i have 3999 cells formatted "bold" and another 2 formatted "underlined"
this should not count 4001. True?


My workbook has about 15 worksheets with each fits to 2 printing pages.

 

[serdar]

Adding Jerome's question below:


So my point is : How to trace the evolution of the current number of
different cell formats in my active workbook? Which property of which
object will give me this figure?

Thank you
Jérôme

 

[Dodo]

This error occcurs when i add some worksheets to a workbook from
another one.

Excel specifications and limits:

http://office.microsoft.com/en-us/assistance/HP051992911033.aspx

 

[Earl Kiosterud]

Sercar,

The way I understand it it this. Excel creates a thing called a style for
any combination of all possible formats for a cell. More than one cell can
use that style if it wants the same formats. There can be 4000 styles, then
it runs out. If you have 3999 cells formatted for bold, that's one style.
You have 3999 more styles you can have. The two underlined cells use a
second style. It has nothing to do with how many cells are formatted using
any style.

 

[serdar]

Well then i am almost sure that i dont have more than a hundred style in my
workbook. Why i am still getting an error? It would be real good if i could
trace the number of cell formats used in the workbook by VB for instance.

 

[David McRitchie]

Every combination of
borders (8 per cell inside/outside): width, color, style
font: italic, bold, regular, fontsize, strikeout, super/subscript
interior: pattern, color
number format: you can make up your own custom formats as well

Those are just off of my memory.

It is best to format the entire worksheet at once, rather than
one cell at a time. Much more efficient.

 

[serdar]

The workbook i work on is sent to us by a state organization and definitely
designed by novice users. We are having trouble to spot where is the
problem. They make lots of other mistakes. We have just noticed they have
drawn hundreds of useless drawing objects near a corner of a worksheet etc.

As i said before, i would be perfect to trace the number of cell formats
used in a workbook.

 

[Norman Jones]

Hi Serdar,

Perhaps the following code from Leo Heuser may be of interest.

If the 'No' option is selected in response to Leo's opening message box, a
list of all used (and unused) formats is produced.

'=============================>>
Sub DeleteUnusedCustomNumberFormats()
'(e-mail address removed), 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("A3")
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
Next Counter
With ActiveSheet.Columns("A:C")
.AutoFit
.HorizontalAlignment = xlLeft
End With
If Answer = vbYes Then
DataStart = Range(Cells(1, 3), _
Cells(EndRow, 3)).Find("").Row + 1
DataEnd = Cells(DataStart, 3).Resize(EndRow, 1). _
Find("").Row - 1
On Error Resume Next
For Each Cell In Range(Cells(DataStart, 3), _
Cells(DataEnd, 3)).Cells
Workbooks(ActWorkbookName).DeleteNumberFormat _
(Cell.NumberFormat)
Next Cell
End If
Finito:
Set Cell = Nothing
Set Sh = Nothing
Set Buffer = Nothing
End Sub
'<<=============================

 

[Earl Kiosterud]

Serdar,

They call them "format combinations," not styles, and I'm not sure they're
actually called styles internally (I'm not sure where I read that they're
called styles -- maybe I made it up). They aren't part of the
workbook.styles collection. That collection has the normal style, a handful
of commonly used styles (currency, etc), and the Normal style, the one used
when you haven't done any formatting on a cell, or have removed all
formatting, as with Edit - Clear - Formats, and any styles you've added with
Format - Styles. I haven't been able to find anything in vba that would
tell you how many "format combinations" have been used. It would certainly
be useful.

 

[serdar]

Yes, i mean cell formats. I used the word "styles" cos Microsoft refers them
so in this article as "Cell styles in a workbook":
http://office.microsoft.com/en-us/assistance/HP051992911033.aspx

thanks.

 

[Earl Kiosterud]

Serdar,

HUH! There it is! HUH! There it is! They don't differentiate between
user-defined styles (Format - Style) or internally-generated styles (created
with each unique combination of formats). To determine if they're both
included,
I wrote a vba routine to put unique combinations of font color, fill color,
and border color. It crashed at cell 3821. After that, I was unable to add
a style (Format Style), or manually format a cell. Either gave the "Too
many format combinations" message. Apparently, they're all lumped together.
And it ain't 4000 exactly.

This is significant: If you manually clear or delete (or even delete the
sheet), it still doesn't allow any more cell formatting or style adding.
It's as if the styles don't get cleared when no longer used in any cell.
That might account for your situation. You may have to copy the stuff to a
new workbook.

Here's the routine, if you want to play with it.

Sub TestStyles()
Dim F As Integer
Dim i As Integer
Dim B As Integer
Dim StyleCount As Integer

Range("A:A").Clear
For F = 1 To 56 ' font colorindex
For i = 1 To 56 ' interior (fill) colorindex
For B = 1 To 56 ' top border colorindex
StyleCount = StyleCount + 1
Cells(StyleCount, 1).Select ' watch it run
Cells(StyleCount, 1).Value = StyleCount ' put style count in cell
Cells(StyleCount, 1).Font.ColorIndex = F ' set font color
Cells(StyleCount, 1).Interior.ColorIndex = i ' set fill color
With Cells(StyleCount, 1).Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThick
.ColorIndex = B ' set border color
End With
Next B
Next i
Next F

End Sub
--
Earl Kiosterud
www.smokeylake.com

Yes, i mean cell formats. I used the word "styles" cos Microsoft refers
them
so in this article as "Cell styles in a workbook":
http://office.microsoft.com/en-us/assistance/HP051992911033.aspx

thanks.

 

[serdar]

That might account for your situation. You may have to copy the stuff to a
new workbook.

Thanks, that was the solution i suggested to my friend, and im gonna try it
first time i arrived at the office again. I was even thinking to email the
workbook to some excel pro (like u to check it out. Thanks for the script
also.