Too Many Custom Formats

Too Many Custom Formats

Post by Paul » Tue, 19 Mar 2002 00:24:53



I have periodic trouble with too many custom formats.  I
do not intentionally save custom formats but they are
being added & excel overloads at some point.  Then i have
to deltete custom formats, one by one (do not know if
there is a clear all - does not appear so). Any
experience? How can I fix this problem.  Thanks.

 
 
 

Too Many Custom Formats

Post by Jan Karel Pieters » Tue, 19 Mar 2002 16:51:03


Hi,

This code gets rid of obsolete formats:
Option Explicit
Dim lNumberformatcount As Long
Dim sNumberformats() As String
Dim sCustomNumberformats() As String

Sub RemoveUnusednumberformats()
Dim oWorkbook As Workbook
Dim oWorksheet As Worksheet
Dim sName As String
Dim rCell As Range
Dim lCounter As Long
    Set oWorkbook = ActiveWorkbook
    GetCustomFormats
    For Each oWorksheet In oWorkbook.Worksheets
        For Each rCell In oWorksheet.UsedRange
                lCounter = lCounter + 1
                ReDim Preserve sNumberformats(lCounter)
                sName = rCell.NumberFormat
                sNumberformats(lCounter) = sName
        Next
    Next
    For lCounter = lNumberformatcount To 1 Step -1
            If Application.IsNA(Application.Match
(sCustomNumberformats(lCounter), sNumberformats, 0)) =
True Then
                On Error Resume Next
                oWorkbook.DeleteNumberFormat
(sCustomNumberformats(lCounter))
            End If
    Next
    Set oWorkbook = Nothing
    Set rCell = Nothing
End Sub
Sub GetCustomFormats()
    Dim sTemp As String
    ReDim sCustomNumberformats(10)
    lNumberformatcount = 1
    sCustomNumberformats(0) = " "
    ActiveWorkbook.Worksheets.Add
    Do
        SendKeys "{TAB}{end}{TAB}{TAB}{down}~"
        Application.Dialogs(xlDialogFormatNumber).Show
        sTemp = ActiveCell.NumberFormat
        sCustomNumberformats(lNumberformatcount) = sTemp
        If sCustomNumberformats(lNumberformatcount) =
sCustomNumberformats(lNumberformatcount - 1) Then
            Exit Do
        End If
        lNumberformatcount = lNumberformatcount + 1
        ReDim Preserve sCustomNumberformats
(lNumberformatcount + 1)
    Loop
    MsgBox lNumberformatcount
    Application.DisplayAlerts = False
    ActiveSheet.Delete
    Application.DisplayAlerts = True
End Sub

How to enter a macro (=VBA code):

  1. Open the Excel file you want to add the code to.

  2. Choose Tools, Macro, Visual Basic Editor to open the
     Visual Basic Editor (or press Alt+F11). This is the
     design environment that stores the VBA code. If this
is
     the first time you have opened the editor, you will
     probably see three windows: the Project window, the
     Properties window, and the Code window.

  3. In the Project window, select the name of the workbook
     you want to add the sub to (one project is listed for
     each open file).

  4. After selecting the project icon, choose Insert,
Module.
     This inserts a VBA code module into the project and
     places the insertion point in the Code window of that
     module.

  5. You can either type the snippet code exactly as
written,
     or you can copy the code directly from this message
and
     paste it into the Code window.

  6. Close the Visual Basic Editor window.

Regards,

Jan Karel Pieterse
Excel TA/MVP

Quote:>-----Original Message-----

>I have periodic trouble with too many custom formats.  I
>do not intentionally save custom formats but they are
>being added & excel overloads at some point.  Then i have
>to deltete custom formats, one by one (do not know if
>there is a clear all - does not appear so). Any
>experience? How can I fix this problem.  Thanks.
>.


 
 
 

Too Many Custom Formats

Post by Chris Nichola » Thu, 21 Mar 2002 01:46:26


If you are in Excel 2000 or greater try round tripping the file through
HTML.

Basically Save the entire workbook as HTML no interactivity needed.  Close
the application and reopen ( to release resources ).  Re-open Excel and then
from the MRU list on the File Menu open the HTML and then Save As an Excel
Workbook.

This process clears alot of stuff from the file including unused styles that
might have been left behind by a Macro virus such as Laroux.

--
Chris Nicholas, MCSE
Please no Direct Email responses.


Quote:

> I have periodic trouble with too many custom formats.  I
> do not intentionally save custom formats but they are
> being added & excel overloads at some point.  Then i have
> to deltete custom formats, one by one (do not know if
> there is a clear all - does not appear so). Any
> experience? How can I fix this problem.  Thanks.

 
 
 

1. Conditional Formatting - problem with Custom format validation

Dear All

Excel 97 SR-2

Is this possible ?

I am trying to use Conditional Formatting to determine whether an expense
claim exceeds 4000 miles.  If it does the employee is reminded to complete a
new claim.  The mileage claim figures can be entered in multiple cells
(J11 - J25).

Unfortunately I need to exclude certain types of employees (those with
company cars) as the mileage restriction does not apply.

In the conditional formatting box I am using the following formula:
=AND(user_type<>"Company Car",OR(miles_bf>3999,miles_bf+SUM($J$11:$J$25)
<=4000))

miles_bf is cell J10.

The error alert is using the style Stop.

This works fine if the user_type is anything other than "Company Car".

Unfortunately when the user type is "Company Car" the Conditional Formatting
fails no matter what mileage figure is entered.  Not what I was trying to
achieve :-(

Any help resolving this problem would be appreciated.

Regards

Antony

2. worksheet error

3. Too many different cell formats (different than No more custom formats can be added)

4. Access Version 2002

5. XL97 Bug: Format, Cell: Custom format: "General;General;;"

6. Accessing forms in an external database

7. "No More Custom Formats Can be Added"?

8. outlook 2000

9. Custom Format date changing to USA date

10. custom formats

11. Custom format for pounds and ounces???

12. custom format on %

13. Custom Formatting