Too many different cell formats!

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.

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

"serdar" <[email protected]> wrote in
news:[email protected]:

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

Excel specifications and limits:

http://office.microsoft.com/en-us/as...992911033.aspx


--

It is I, DeauDeau
(Free after monsieur Leclerc in 'Allo, 'allo)

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.
--
Earl Kiosterud
www.smokeylake.com

"serdar" <[email protected]> wrote in message
news:[email protected]...
> 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.
>
>
>
>
>

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.


"Earl Kiosterud" <[email protected]>, haber iletisinde şunları
yazdı:[email protected]...
> 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.
> --
> Earl Kiosterud
> www.smokeylake.com
>
> "serdar" <[email protected]> wrote in message
> news:[email protected]...
> > 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.
> >
> >
> >
> >
> >
>
>

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.

---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"serdar" <[email protected]> wrote in message news:t%[email protected]...
> 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.
>
>
> "Earl Kiosterud" <[email protected]>, haber iletisinde şunları
> yazdı:[email protected]...
> > 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.
> > --
> > Earl Kiosterud
> > www.smokeylake.com
> >
> > "serdar" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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.
> > >
> > >
> > >
> > >
> > >
> >
> >
>
>

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.


"David McRitchie" <[email protected]>, haber iletisinde şunları
yazdı:u#[email protected]...
> 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.
>
> ---
> HTH,
> David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
> My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
> Search Page: http://www.mvps.org/dmcritchie/excel/search.htm
>
> "serdar" <[email protected]> wrote in message
news:t%[email protected]...
> > 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.
> >
> >
> > "Earl Kiosterud" <[email protected]>, haber iletisinde şunları
> > yazdı:[email protected]...
> > > 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.
> > > --
> > > Earl Kiosterud
> > > www.smokeylake.com
> > >
> > > "serdar" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > 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.
> > > >
> > > >
> > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>
>

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()
'[email protected], 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
'<<=============================


---
Regards,
Norman



"serdar" <[email protected]> wrote in message
news:[email protected]...
> 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.

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.
--
Earl Kiosterud
www.smokeylake.com

"serdar" <[email protected]> wrote in message
news:%[email protected]...
> 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.
>
>
> "Earl Kiosterud" <[email protected]>, haber iletisinde şunları
> yazdı:[email protected]...
>> 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.
>> --
>> Earl Kiosterud
>> www.smokeylake.com
>>
>> "serdar" <[email protected]> wrote in message
>> news:[email protected]...
>> > 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.
>> >
>> >
>> >
>> >
>> >
>>
>>
>
>

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/as...992911033.aspx

thanks.


"Earl Kiosterud" <[email protected]>, haber iletisinde şunları
yazdı:[email protected]...
> 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.
> --
> Earl Kiosterud
> www.smokeylake.com

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

"serdar" <[email protected]> wrote in message
news:[email protected]...
> 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/as...992911033.aspx
>
> thanks.
>
>
> "Earl Kiosterud" <[email protected]>, haber iletisinde şunları
> yazdı:[email protected]...
>> 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.
>> --
>> Earl Kiosterud
>> www.smokeylake.com
>
>

> 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.

I had this happen to a very important spreadsheet that would have been almost impossible to re-create. The file would open on occasion yet I couldn't edit any cells.

I searched for a macro or de-bug program and found a wonderful solution. Go to this website and download this program. It is called xlsgen system reduction tool and is a Microsoft Excel delivered product. The cost is $39 USD but it cures the problem for good. The program locates all duplicate cell styles, corrects them and compresses the similar styles. I ran the program against my file. The problem is solved and the file is perfect once again.

http://xlsgenreduction.arstdesign.com/

Good Luck!!

David

This utility saved me a lot of bother for a similar issue, you should look into it:

https://sergeig888.wordpress.com/200...ful-utilities/

Edit: Oops, did not realise this post was so old...