Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Too many different cell formats
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 if anyone have idea. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Too many different cell formats
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 wrote in message ... 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 if anyone have idea. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Too many different cell formats
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? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Too many different cell formats
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? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Too many different cell formats
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Too many different cell formats
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" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copying formats - column widths, formats, outlining to worksheets | Excel Worksheet Functions | |||
Inheriting cell formats when using absolute cell references | Excel Discussion (Misc queries) | |||
two formats in one cell | Excel Discussion (Misc queries) | |||
Copy and link formats from cell to cell | Excel Discussion (Misc queries) | |||
Formats: Too many different cell formats error message | Excel Programming |