I am getting the error "Too Many Cell Formats" and the Microsoft help does not help fix it nor can I do anything in excel.
Can anyone help?
I am getting the error "Too Many Cell Formats" and the Microsoft help does not help fix it nor can I do anything in excel.
Can anyone help?
When does the error occur, when you open the workbook.
Are you not able to select the cells and change the format?
Steve
"Nearly all men can stand adversity, but if you want to test a man's character, give him power."
-Abraham Lincoln
I'm not able to change formats or add new rows.
Do you have any sheet specific code running?
can you post your workbook here?
Steve
"Nearly all men can stand adversity, but if you want to test a man's character, give him power."
-Abraham Lincoln
Is the sheet protected, does it occur on all sheets?
Steve
"Nearly all men can stand adversity, but if you want to test a man's character, give him power."
-Abraham Lincoln
There is VBA coding so that you have to enter a password to see a sheet.
Yes it occurs on all sheets. I tried deleting all but 1 sheet and it still does it.
I tried posting the workbook but it says its too big even when I try to delete stuff.
zip it up spaz and try it again......Is it just this one workbook and once again is the sheet protected?
Steve
"Nearly all men can stand adversity, but if you want to test a man's character, give him power."
-Abraham Lincoln
What Excel version are you running? 2003 contains "Excel specifications and limits" in the help file
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
Lucas - no the spreadsheet is not protected (except for the password)
Even when I zip it, its too big - wont let me upload.
MDMac - I am using 2003. I look it up in the help file but it just shows me what the specs and limits are - not how to fix it.
how big?
Steve
"Nearly all men can stand adversity, but if you want to test a man's character, give him power."
-Abraham Lincoln
I get that annoyance ever so often, and when I investigated, the conclusion is to use fewer formats. Think of Excel keeping a list of all the different format combinations for all the cells in a huge, but finite sized, table. If you do a lot of individual cell formatting, then that table can fill up. It helps a little for a short time to exit Excel, and start it up again. I think it must add a little padding to the table when you do that.
I've seen a couple of places that you can reduce the table requirements somewhat by selecting at the same time as many of the identically formatted cells as you can, then apply the format.
HTH, Sid
It also helps to use Styles, which are often overlooked in Excel.
Hold it ... you can't just say that without more explanation and/or proof. That could be a very important workaround if it is indeed true. Could you elaborate?
Hi Spaz,
Once I had the password to open the workbook I was able without any problem to insert rows and reformat cells. I think you are missing a step.
When the workbook opens and you have entered your password you are then presented with a dialog which asks if you wish to open the file read-only.....I think you must be clicking on yes. Try clicking on No
On another issue, why is the vba project password protected? There is no code at all
Steve
"Nearly all men can stand adversity, but if you want to test a man's character, give him power."
-Abraham Lincoln
There are codes, I took them out before sending it to you because they make the file even larger.
Try "format cell" of a few lines or a column - thats where I also get the error.
I have tried formating columns, multiple rows....what specific type of formatting are you trying to apply as I cannot duplicate your problem.
Steve
"Nearly all men can stand adversity, but if you want to test a man's character, give him power."
-Abraham Lincoln
There is a limit of approximately 4000 different cell format combinations in Excel. For example, if you have bold and italic in one cell, and bold in another, that's 2 combinations. It's pretty hard to hit this limit, but I've seen it done. Often the difference between the formats is not readily noticable, but any difference in any option available under Format | Cell is classified as a separate combination.
Although styles are predominately used in Word, you can also set them in Excel. The simplest method is to format a cell as you would normally, then select Format | Style. Type a name for your style (such as Heading) and select Add. All the formatting options have now been defined in the style, and is automatically added to your cells each time you apply the style.
The advantages are:
Unfortunately the Style box is not included on any of the standard toolbars, but you can easily customise the toolbar to add the style drop-down (as you will have seen in Word).
- each cell to which you have applied the style is identical;
- if you make a change to the style (Format, Style, Modify) all cells that are using that style will automatically update to the new format;
- because the formatting of all cells using the style is identical, it's much more difficult to ever hit the limit of formatting combinations in a single workbook.
THats great but how can I fix my current worksheet? When I try to select a column to format cells, it gives me the error.
I think something is going on with your setup. What version of excel are you using. I have not been able to recreate your error with the file you sent me. Please tell me exactly the steps you are taking that is causing the error.....select entire rows 8 through 12. right click and select format cells......what format are you applying?, etc. so that I can reproduce your error.
Steve
"Nearly all men can stand adversity, but if you want to test a man's character, give him power."
-Abraham Lincoln
If its a column....which column.....do you select the entire column?
Steve
"Nearly all men can stand adversity, but if you want to test a man's character, give him power."
-Abraham Lincoln
Posting Permissions
Reply With Quote
