[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1988
  • Last Modified:

"Too many format cells" error masage in excel

While working on an excel file I've got the following error masage : "Too many format cells". I don't mind see this masage but after saving and trying to reopen I couldn't open the file by given this masage again. After a deep investigaion I realized that the Excel keep in Format -> Style a lot of format styles while working on a file. To be able to open the file all styles should be deleted from this location. If you open a very large file with a lot of formating cells you will find hundreds of styles, beside 7 of them the rest are not necessery. The delete botten allow you to delete only one style each time.
Do you know how can I delete all non necessery styles efficiency?

Thanks
nitsali
0
nitsali
Asked:
nitsali
  • 3
  • 2
1 Solution
 
ahammarCommented:
You almost defineately have the Laurux virus. (I'm not sure if that's how you spell it) It's a nasty thing.
It does a lot more than what you know already. There are probably also some hidden sheets with illegal names and they are very difficult to delete. MS's Excel recovery macro won't even run on it because of the illegal names. Excel refuses to let you do anything with them because they have illegal names. There are probably literally thousands and thousands of styles listed if you investigate further, and there is no way to delete more than 1 at a time. Also, with most of the infected files I worked with, there are about 70 of them you can't delete because the delete button will be grayed out.

I have written a macro that will clean a file from the effects of the virus, but it does not delete the virus. It will recreate your file, leaving all the extra formats behind. It only keeps the 6 or 7 default ones. It will leave all the illegally named sheets behind also. It has one fault that I know of though. It will not work on a file that has a chart in it. If you have any charts, I think you will have to delete them first, then recreate them in the newly created file.

You will have to remove the virus first, then if you want a copy of the Excel file I have to clean it up, e-mail me at:

ahammar@cyberhighway.net


Once you remove the virus, the macro I have will clean up any files that were infected, so don't worry about removing all the styles or hidden sheets if you want to try my macro.
It's easy to use. Just open it, click run, enter the file you want to clean, and that's it.



Here is some info on how to remove the virus
(This was originally posted by argmyster as an answer to a question that I had.) If you want to read the entire thread, here is the url:
http://www.experts-exchange.com/Computers/Applications/MS_Office/Q_10125585.html

Chances are you have the Laroux virus or at least a strain of
it.Your Style problem what commonly happens to infected
files.Look at some of your styles do they have the word Laroux or
PLDT or CAR, Extra, any some other unknown names.

Detecting and Removing the PLDT, CAR, and SGV Macro Viruses
-----------------------------------------------------------

 - If the PLDT macro virus has infected any of your workbooks, the
 workbook Pldt.xls will be found in one of the following folders on
 your computer:

C:\Excel\Xlstart

C:\Program Files\Microsoft Office\Office\Xlstart

 Also, any workbooks that are infected by the macro virus will
contain
 a Visual Basic module called "pldt".

 - If the CAR macro virus has infected any of your workbooks, the
 workbook Car.xls will be found in one of the following folders on
 your computer:

C:\Excel\Xlstart

C:\Program Files\Microsoft Office\Office\Xlstart

 Also, any workbooks that are infected by the macro virus will
contain
 a Visual Basic module called "car".

 - If the SGV macro virus has infected any of your workbooks, the
 workbook Sgv.xls will be found in one of the following folders on
 your computer:

C:\Excel\Xlstart

C:\Program Files\Microsoft Office\Office\Xlstart

 Also, any workbooks that are infected by the macro virus will
contain
 a Visual Basic module called "sgv".

To remove the PLDT, CAR, and SGV macro viruses from your
workbooks,
follow these steps.

In Microsoft Excel 97
---------------------

 1. On the Tools menu, click Options. Click the General tab. Click the

Macro Virus Protection checkbox, and then click OK.

 2. Quit Microsoft Excel 97.

 3. Using Windows Explorer, go to the C:\Program Files\Microsoft
Office\Office\Xlstart folder.

 4. If it exists, select the file Pldt.xls. On the File menu, click
Delete. Click Yes if you are asked whether to move the file to the

Recycle Bin.

 5. If it exists, select the file Car.xls. On the File menu, click
Delete. Click Yes if you are asked whether to move the file to the

Recycle Bin.

 6. If it exists, select the file Sgv.xls. On the File menu, click
Delete. Click Yes if you are asked whether to move the file to the

Recycle Bin.

 7. Start Microsoft Excel 97.

 8. Open a workbook that you believe to be infected with the PLDT,
CAR,
or SGV macro virus.

If you receive the following message

 The workbook you are opening contains macros. Some macros
may
 contain viruses that could be harmful to your computer.

 If you are sure this workbook is from a trusted source, click
 'Enable Macros'. If you are not sure and want to prevent any
 macros from running, click 'Disable Macros'.

click Disable Macros.

 9. On the Tools menu, point to Macro, and then click Visual Basic
Editor.

10. Click Project Explorer on the View menu to make sure the Project

Window is visible.

11. In the Project window, click the plus sign (+) to the left of the
word "Modules" below the name of the workbook you just opened.


If a module named "pldt", "car", or "sgv" is listed, right-click the
module name. On the shortcut menu, click "Remove <module>".
Click No
when you are asked whether to export the module.

12. On the File menu, click Close And Return To Microsoft Excel.

13. On the Format menu, click Style.

14. In the Style Name list box, look for styles whose names contain
"pldt", "car", "sgv", or "laroux". If you see such a style listed,
select it. Then, click Delete. Repeat this step until no more such
styles remain.

15. On the File menu, click Save. On the File menu, click Close.

16. Repeat steps 8 through 15 for all workbooks that you believe to
be
infected with the PLDT, CAR, or SGV macro virus.

Also, if any other workbooks, such as Personal.xls, are listed in
the
Project window in the Visual Basic Editor, click the plus sign to
the
left of the word Modules below each workbook's name. If any
modules
named "pldt", "car", or "sgv" are displayed, right-click the module
name, and then click "Remove <module>" on the shortcut menu.

Until you are absolutely certain that the PLDT, CAR, and SGV macro
viruses
have been completely removed from your computer, click Disable
Macros
every time you open a workbook. If you open a workbook that
contains the
PLDT, CAR, or SGV macro virus and click Enable Macros, the macro
virus will
begin to infect your workbooks again.

NOTE: If you have exchanged workbooks with anyone else, you
should alert
them to the possibility that their workbooks may also be infected by
the
PLDT, CAR, or SGV macro virus.

In Microsoft Excel 5.0 or 7.0
-----------------------------

 1. Quit Microsoft Excel.

 2. Using Windows Explorer, go to the Xlstart folder for your version
of
Microsoft Excel.

 3. Select the file Pldt.xls, and click Delete on the File menu. Click
Yes
if you are asked if you want to move the file to the Recycle Bin.

 4. Select the file Car.xls, and click Delete on the File menu. Click
Yes
if you are asked if you want to move the file to the Recycle Bin.

 5. Select the file Sgv.xls, and click Delete on the File menu. Click
Yes
if you are asked if you want to move the file to the Recycle Bin.

 6. Start Microsoft Excel.

 7. Open a workbook that you believe to be infected with the PLDT,
CAR, or
SGV macro virus. As you open the workbook, hold down the
SHIFT key;
this will prevent any Auto_Open macros in the workbook from
running.

 8. On the Format menu, point to Sheet, and click Unhide. If "pldt",
"car", or "sgv" is listed in the Unhide Sheet list box, click it, and
then click OK.

 9. On the Edit menu, click Delete Sheet. Click OK to permanently
delete the
sheet.

10. On the Format menu, click Style.

11. In the Style Name list box, look for styles whose names contain
"pldt", "car", "sgv", or "laroux". If you see such a style listed,
select it. Then, click Delete. Repeat this step until no more such
styles remain.

12. On the File menu, click Save. On the File menu, click Close.

13. Repeat steps 7 through 12 for all workbooks that you believe to
be
infected with the PLDT, CAR, or SGV macro virus.

Also, if you have a personal macro workbook (Personal.xls), you
may need
to unhide it (on the Window menu, click Unhide), perform steps 8
and 9,
and then rehide the personal macro workbook (on the Window
menu, click
Hide). When you quit Microsoft Excel, click Yes to save changes
to the
personal macro workbook.

If you are uncertain as to whether or not a workbook is infected
with the
PLDT, CAR, or SGV macro virus, hold down the SHIFT key while you
open the
workbook, and then perform steps 8 through 10.

NOTE: If you have exchanged workbooks with anyone else, you
should alert
them to the possibility that their workbooks may also be infected by
the
PLDT, CAR, or SGV macro virus.

Using Third-party Anti-virus Software to Remove Macro Viruses
-------------------------------------------------------------

Some third-party anti-virus programs have developed updated
signature files
that allow you to detect and remove macro viruses such as the
PLDT, CAR,
and SGV macro viruses. For information about updated signature
files, check
the Web site of the company that developed your anti-virus
program.

The following are Web addresses for some commonly used anti-virus
programs.

 Program Web Address
 ----------------------------------------------------------

 Norton AntiVirushttp://www.symantec.com/nav/
 McAfee VirusScanhttp://www.mcafee.com/down/upgrade.asp
 F-Prothttp://www.datafellows.com/f-prot/
 Cheyenne InocuLAN http://www.cheyenne.com/virusinfo/ 




Also, check out my first online demo application at:
http://www3.50megs.com/ahammar/CatchInput/catchinput.html

Merry Christmas

Cheers!
ahammar
0
 
vbkidCommented:
Hi nitsali & ahammar,
I too had the same problem.What happened was when I tried to format a cell Excel gave an error saying that 'Too many cell formats'. Then when I closed & opened the file again & deleted few rows that needs formatting, and reformatted them the way I want & it worked. I never suspected that is a virus. ahammar, thanks for the info. Now I have another error, may be similiar, I had put a list of values in one sheet and had a drop down list referring to this in another sheet. The application broke, saying, 'Not enough available resources'. I am working on it.


vbkid.
0
 
ahammarCommented:
nitsali:
I just thought of something. Even if it isn't the virus, (but I'm almost positive it is), the macro I was referring to will still delete all the styles except for the default ones. Let me know if you want it.

vbkid,
I haven't seen the virus cause the other error you mentioned, but it's possible. The virus isn't the only thing that causes the 'Too many cell formats' error, but the way nitsali described it, I'm pretty sure it is. That doesn't mean that you had/have the virus, but there is a chance of it.

You better join in on my New Years game....:-)


Cheers!
ahammar
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
nitsaliAuthor Commented:
ahammar
First of all thank you very much for the long and detailed answer.
Like vbkid I didn't even though about a virus as the cause. But I will check it out.
About the Macro which deletes the styles, I've already done it before, thanks. I just though there is a way to get into some definition which reset this bank of styles.
For the Virus information, Its greate, I will check and use it.

By the way, Is your macro jump on the styles which are grayed and couldn't be deleted?

Thanks again, good luck and Happy new year
nitsali
0
 
ahammarCommented:
Hi nitsali,
You are welcome. If I understand your question, then yes. The macro also gets rid of the ones that gray out the delete button.
There is no way to reset the styles. I know that for sure because Microsoft told me that directly.
Actually, there is no way to really delete the grayed out ones, not even with a macro. The way my macro does it is by openning a new workbook, and moveing everything to it except for the things you don't want. It was a difficult thing to do and took me a long time (although I could probably do it a lot faster now because I know a lot more than I did then) but it works really nice. I sent it to 2 people so far and they were both happy with it. One of them even sent me a gift certificate for 100.00 because they used it to clean up over 400 files.
Anyway, enough bragging....
If I missunderstood your question, let me know.
You are welcome to have a copy of the macro if you want it.

Cheers!
ahammar
0
 
nitsaliAuthor Commented:
You did a great job, it will be nice to have your macro.
nitsali@hotmail.com
thanks again
nitsali
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now