get Too many different cell formats

[Sunil Gajjar]

Is there any way to get a message being displayed when an excel file reaches
3000 different cell format count?

By doing so we can atleast avoid the problem of file corruption due to no of
different cell formats.....

Plz let me know at [email protected] if anyone have idea.

[Peter T]

AFAIK there is no direct way to return the number of unique cell formats. At
least not without not without a considerable amount of code to work it out
from first principles. Or, applying new unique unique formats in a bakup
until you hit the limit - discard the file be keep a count of how many
successfully applied.

Regards,
Peter T

"Sunil Gajjar" <Sunil [email protected]> wrote in message
news:[email protected]...
> Is there any way to get a message being displayed when an excel file
reaches
> 3000 different cell format count?
>
> By doing so we can atleast avoid the problem of file corruption due to no
of
> different cell formats.....
>
> Plz let me know at [email protected] if anyone have idea.

[David McRitchie]

Hi Sunil (and Peter) with requested email copy to Sunil,
Slow Response, Memory Problems, and Speeding up Excel
http://www.mvps.org/dmcritchie/excel...sp.htm#formats

which will send you to Leo Heuser's posting ( 2001-05-06 in programming)
http://google.com/groups?selm=OxP9cg...%40tkmsftngp02

Leo's code deletes unused formats, but at the very beginning it indicates
the number of formats and gives you a choice of whether to
continue or not. I would be a good idea to practice on a copy
of your workbook, if you use the full subroutine.

You could put the first part into Auto_Open in a code module
or in Workbook_Open in "Thisworkbook" class module, with
an informative message if over your threshold.
---
HTH, Please keep discussion in newsgroup, Leo would
be at least as interested in any comments as anyone else.
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

>
> "Sunil Gajjar" <Sunil [email protected]> wrote ...
> > Is there any way to get a message being displayed when an excel file
> > reaches 3000 different cell format count?

[Peter T]

Hi David,

I love it - and I will call it Leo Heuser's NumberFormat Manager !

It's frustrating there doesn't appear to be any other way to return all
built-in and custom number formats other than with Leo's method of looking
at the FormatNumber dialog (neat use of SendKeys too). Intuitively, they
must be stored somewhere more accessible (hidden namespace perhaps ?).

I'm not sure though if it assists with the "Too many cell formats" issue.
Leo's code will, if requested, remove unused Custom number formats. That's
great, but I don't think it would change the overall formats count (?). To
return the formats count would require something akin to Leo's approach but
massively extended, as I alluded to previously.

Intermittently after running Leo's code, I found my Num-Lock disabled. This
seems quite a common problem with SendKeys. I reset with code I first saw
posted by Dave Peterson:

'' top of module
Declare Function SetKeyboardState _
Lib "User32" (kbArray As Byte) As Long

'' call from near the end of Leo's code
Sub ResetNumLock()
Dim KeyState(0 To 255) As Byte
KeyState(&H90) = 1 ' 1 on, 0 off
SetKeyboardState KeyState(0)
End Sub

Off topic and onto "new" Beta Google groups. The link to Leo's post brings
up a "proportional font" page that messes up the code - inserts a number of
long hyphens, chr(173). Might be easier to click link to "fixed font", or in
"old" Google style here:
http://tinyurl.com/3m7xb

Regards,
Peter T

"David McRitchie" <[email protected]> wrote in message
news:#[email protected]...
> Hi Sunil (and Peter) with requested email copy to Sunil,
> Slow Response, Memory Problems, and Speeding up Excel
> http://www.mvps.org/dmcritchie/excel...sp.htm#formats
>
> which will send you to Leo Heuser's posting ( 2001-05-06 in programming)
> http://google.com/groups?selm=OxP9cg...%40tkmsftngp02
>
> Leo's code deletes unused formats, but at the very beginning it indicates
> the number of formats and gives you a choice of whether to
> continue or not. I would be a good idea to practice on a copy
> of your workbook, if you use the full subroutine.
>
> You could put the first part into Auto_Open in a code module
> or in Workbook_Open in "Thisworkbook" class module, with
> an informative message if over your threshold.
> ---
> HTH, Please keep discussion in newsgroup, Leo would
> be at least as interested in any comments as anyone else.
> 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
>
> >
> > "Sunil Gajjar" <Sunil [email protected]> wrote ...
> > > Is there any way to get a message being displayed when an excel file
> > > reaches 3000 different cell format count?
>
>

[David McRitchie]

Hi Peter,
Leo's code would reduce the number of formats, because that is exactly what his code was written for. It eliminates usused formats
and gives you a chance to eliminate others later.

Yes the Beta Google Groups is and has been a disaster since
Nov 2004. You could use the google.uk.co site which not afflicted with
the Beta yet, but that's just part of divide and conquer tactics. To specifically address being able to see code as posted, you
could look at the "Show original" which they hide in "show options".
http://www.mvps.org/dmcritchie/excel...oglebetagroups
read about link-hijacking by Google at
http://www.google-watch.org/toolbar.html
---
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

"Peter T" <peter_t@discussions> wrote in message
> I love it - and I will call it Leo Heuser's NumberFormat Manager !
>
> > Leo Heuser's posting ( 2001-05-06 in programming)
> > http://google.com/groups?selm=OxP9cg...%40tkmsftngp02
> >
> > Leo's code deletes unused formats, but at the very beginning it indicates
> > the number of formats and gives you a choice of whether to

[Peter T]

Hi David,

We might be slightly at cross purposes, or perhaps I'm missing something.
Leo's code certainly removes unused custom NumberFormats (on request) from
the list in the workbook. But I don't see how that reduces the number of
[used cell] formats.

Quite rightly, it does not change or remove any cell formatting, or remove
from the list a custom number format even if it has only used in a blank
cell.

Regards,
Peter T



"David McRitchie" <[email protected]> wrote in message
news:#[email protected]...
> Hi Peter,
> Leo's code would reduce the number of formats, because that is exactly
what his code was written for. It eliminates usused formats
> and gives you a chance to eliminate others later.
>
> Yes the Beta Google Groups is and has been a disaster since
> Nov 2004. You could use the google.uk.co site which not afflicted with
> the Beta yet, but that's just part of divide and conquer tactics. To
specifically address being able to see code as posted, you
> could look at the "Show original" which they hide in "show options".
> http://www.mvps.org/dmcritchie/excel...oglebetagroups
> read about link-hijacking by Google at
> http://www.google-watch.org/toolbar.html
> ---
> 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
>
> "Peter T" <peter_t@discussions> wrote in message
> > I love it - and I will call it Leo Heuser's NumberFormat Manager !
> >
> > > Leo Heuser's posting ( 2001-05-06 in programming)
> > > http://google.com/groups?selm=OxP9cg...%40tkmsftngp02
> > >
> > > Leo's code deletes unused formats, but at the very beginning it
indicates
> > > the number of formats and gives you a choice of whether to
>
>