PDA

View Full Version : Too Many Cell Formats



spaz9876
07-11-2006, 08:26 AM
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?

lucas
07-11-2006, 08:37 AM
When does the error occur, when you open the workbook.
Are you not able to select the cells and change the format?

spaz9876
07-11-2006, 08:39 AM
I'm not able to change formats or add new rows.

lucas
07-11-2006, 08:44 AM
Do you have any sheet specific code running?
can you post your workbook here?

lucas
07-11-2006, 08:45 AM
Is the sheet protected, does it occur on all sheets?

spaz9876
07-11-2006, 09:10 AM
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.

lucas
07-11-2006, 09:15 AM
zip it up spaz and try it again......Is it just this one workbook and once again is the sheet protected?

mdmackillop
07-11-2006, 01:33 PM
What Excel version are you running? 2003 contains "Excel specifications and limits" in the help file

spaz9876
07-11-2006, 01:41 PM
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.

lucas
07-11-2006, 01:58 PM
how big?

Cyberdude
07-11-2006, 02:00 PM
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

geekgirlau
07-11-2006, 04:23 PM
It also helps to use Styles, which are often overlooked in Excel.

Cyberdude
07-11-2006, 08:29 PM
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? :devil2:

lucas
07-13-2006, 09:02 AM
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

spaz9876
07-13-2006, 09:28 AM
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.

lucas
07-13-2006, 09:46 AM
I have tried formating columns, multiple rows....what specific type of formatting are you trying to apply as I cannot duplicate your problem.

geekgirlau
07-14-2006, 03:40 AM
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:

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

spaz9876
07-14-2006, 07:28 AM
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.

lucas
07-14-2006, 07:53 AM
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.

lucas
07-14-2006, 07:54 AM
If its a column....which column.....do you select the entire column?

mdmackillop
07-14-2006, 08:28 AM
If you copy and pastespecial all your cell formats to another workbook, do you still have the same problem?
Here's some code to do that.


Sub CopyFormats()
Dim wb As Workbook, NewWb As Workbook, sh As Worksheet
Set wb = ThisWorkbook
Set NewWb = Workbooks.Add
wb.Activate
With NewWb
For Each sh In wb.Worksheets
sh.Cells.copy
.Sheets.Add after:=.Sheets(.Sheets.Count)
.ActiveSheet.[A1].PasteSpecial Paste:=xlFormats
Next
End With
Set wb = Nothing
Set NewWb = Nothing
End Sub

geekgirlau
07-20-2006, 12:59 AM
Have you tried saving a version of your workbook with all the formats removed? If so, did this resolve the error?