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 he
http://tinyurl.com/3m7xb

Regards,
Peter T

"David McRitchie" wrote in message
...
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 wrote ...
Is there any way to get a message being displayed when an excel file
reaches 3000 different cell format count?