Excel

Delete all charts in a sheet or workbook

Ease of Use

Easy

Version tested with

2000 

Submitted by:

MWE

Description:

xlDelCharts will delete all charts in a worksheet. Its parent, xlDelChartsBook, will sequence through all worksheets in a workbook and call xlDelCharts to delete charts, and then delete all chartsheets in the workbook 

Discussion:

Many applications create lots of charts, some based on worksheet data and some based on VBA arrays. It is not uncommon to end up with dozens of charts, most of which are no longer of use. Manual deletion is easy, but tedious. XlDelCharts will delete all charts in the active worksheet in one operation. To delete all charts and chartsheets in the whole workbook, use xlDelChartsBook. 

Code:

instructions for use 

			
Option Explicit 
 
Sub xlDelCharts( _ 
    Optional xlBookName As String, _ 
    Optional xlSheetName As String, _ 
    Optional InformUser As Boolean = True) 
     '
     '****************************************************************************************
     '       Function    deletes all embedded charts in the target worksheet
     '       Passed Values:
     '           xlBookName [in, string, OPTIONAL] target workbook; default = activeworkbook
     '           xlSheetName [in, string, OPTIONAL] target worksheet; default = activesheet
     '           InformUser [in, boolean, OPTIONAL] flag to indicate if user is to be
     '               informed of progress {default = TRUE}
     '
     '****************************************************************************************
     '
    Dim I           As Integer 
    Dim MsgBxTitle  As String 
    Dim NumCharts   As Integer 
    Dim Rtn         As VbMsgBoxResult 
    Dim xlBook      As Workbook 
    Dim xlSheet     As Worksheet 
     
    MsgBxTitle = "Delete Charts in Worksheet" 
     '
     '           set target workbook
     '
    On Error GoTo xlBookError 
    If xlBookName = vbNullString Then xlBookName = ActiveWorkbook.Name 
    Set xlBook = Workbooks(xlBookName) 
     '
     '           set target worksheet
     '
    On Error GoTo xlSheetError 
    If xlSheetName = vbNullString Then xlSheetName = ActiveSheet.Name 
    Set xlSheet = xlBook.Worksheets(xlSheetName) 
     
    On Error GoTo ErrorHandling 
     '
     '           fetch count of charts; if < 1, exit
     '
    NumCharts = xlSheet.ChartObjects.Count 
    If NumCharts < 1 Then 
        If InformUser = True Then 
            MsgBox "There are no embedded chart to delete in worksheet " & _ 
            xlSheetName, vbInformation, MsgBxTitle 
        End If 
        Exit Sub 
    End If 
     '
     '           tell user what will happen
     '
    If InformUser = True Then 
        Rtn = MsgBox("This procedure will delete all charts" & vbCrLf & _ 
        "embedded in worksheet " & ActiveSheet.Name & vbCrLf & vbCrLf & _ 
        "# charts to be deleted   " & NumCharts & vbCrLf & vbCrLf & _ 
        "OK ?", vbQuestion & vbYesNo, MsgBxTitle) 
        If Rtn <> vbYes Then Exit Sub 
    End If 
     '
     '           delete embedded charts
     '
    For I = NumCharts To 1 Step -1 
        xlSheet.ChartObjects(I).Delete 
    Next I 
     '
     '           tell user what happened
     '
    If InformUser = True Then 
        MsgBox "xlDelCharts" & vbCrLf & vbCrLf & _ 
        "workbook:  " & xlBookName & vbCrLf & _ 
        "worksheet: " & xlSheetName & vbCrLf & _ 
        NumCharts & " chart object(s) successfully deleted", _ 
        vbInformation, MsgBxTitle 
    End If 
     
    Exit Sub 
     '
     '           error handling
     '
xlBookError: 
    MsgBox "xlDelCharts: workbook specified is " & xlBookName & vbCrLf & _ 
    "That workbook is not presently open" & vbCrLf & vbCrLf & _ 
    "No actions taken", vbCritical, MsgBxTitle 
    Exit Sub 
     
xlSheetError: 
    MsgBox "xlDelCharts: worksheet specified is " & xlSheetName & vbCrLf & _ 
    "That sheet is either not part of " & xlBookName & " or is" & vbCrLf & _ 
    "a chartsheet." & vbCrLf & vbCrLf & _ 
    "No actions taken", vbCritical, MsgBxTitle 
    Exit Sub 
     
ErrorHandling: 
    MsgBox "xlDelCharts: error encountered; err = " & Str(Err), vbCritical, _ 
    MsgBxTitle 
     
End Sub 
 
Sub xlDelChartsBook( _ 
    Optional xlBookName As String, _ 
    Optional InformUserSheet As Boolean = False, _ 
    Optional InformUserBook As Boolean = True, _ 
    Optional DelWhich As String = "both") 
     '
     '****************************************************************************************
     '       Function    deletes all chart objects in the target workbook
     '       Passed Values:
     '           xlBookName [in, string, OPTIONAL] target workbook; default = activeworkbook
     '           InformUserSheet [in, boolean, OPTIONAL] flag to indicate if user is to be
     '               informed of progress at sheet level {default = FALSE}
     '           InformUserBook [in, boolean, OPTIONAL] flag to indicate if user is to be
     '               informed of progress at book level {default = TRUE}
     '           DelWhich [in, string, OPTIONAL] indicates what object types will be deleted,
     '               i.e., embedded charts, chartsheets or both:
     '               DelWhich = "charts"         only embedded charts will be deleted
     '               DelWhich = "chartsheets"    only chartsheets will be deleted
     '               DelWhich = "both"           both types will be deleted
     '                    {default = "both"}
     '
     '****************************************************************************************
     '
    Dim MsgBxTitle  As String 
    Dim NumCharts   As Integer 
    Dim NumChSheets As Integer 
    Dim NumSheets   As Integer 
    Dim Rtn         As VbMsgBoxResult 
    Dim strBuffer   As String 
    Dim xlBook      As Workbook 
    Dim xlChSheet   As Chart 
    Dim xlSheet     As Worksheet 
     
    MsgBxTitle = "Delete Charts in Workbook" 
     
     '
     '           set target workbook
     '
    On Error GoTo xlBookError 
    If xlBookName = vbNullString Then xlBookName = ActiveWorkbook.Name 
    Set xlBook = Workbooks(xlBookName) 
     '
     '           test for valid value of DelWhich
     '
    Select Case DelWhich 
    Case "charts", "chartsheets", "both" 
    Case Else 
        MsgBox "xlDelChartsBook: DelWhich arguement is invalid" & vbCrLf & vbCrLf & _ 
        "No actions taken", vbCritical, MsgBxTitle 
        Exit Sub 
    End Select 
    On Error GoTo ErrorHandling 
     
    If InformUserBook = True Then 
         '
         '           collect data to inform user before any charts are deleted
         '
        NumSheets = xlBook.Worksheets.Count 
        For Each xlSheet In xlBook.Worksheets 
            strBuffer = strBuffer & "   " & xlSheet.Name & "   " & _ 
            xlSheet.ChartObjects.Count & vbCrLf 
            NumCharts = NumCharts + xlSheet.ChartObjects.Count 
        Next xlSheet 
        strBuffer = strBuffer & vbCrLf & "ChartSheets to be deleted:" & vbCrLf 
        NumChSheets = xlBook.Charts.Count 
        If NumChSheets > 0 Then 
            For Each xlChSheet In xlBook.Charts 
                strBuffer = strBuffer & "   " & xlChSheet.Name & vbCrLf 
            Next xlChSheet 
            strBuffer = strBuffer & vbCrLf & _ 
            "NOTE: Excel would normally warn you about the impending" & vbCrLf & _ 
            "deletion of any chartsheet.  Those warnings will be turned" & vbCrLf & _ 
            "off to eliminate the need for you to respond to each prompt" & vbCrLf & _ 
            "and turned back on after deleting the charsheets." & vbCrLf 
        End If 
         '
         '           inform user of what will happen and test for agreement
         '
        Rtn = MsgBox("This procedure will delete all charts and chartsheets" & vbCrLf & _ 
        "in workbook " & xlBookName & vbCrLf & vbCrLf & _ 
        "Worksheets to be examined and charts in each: " & vbCrLf & strBuffer & _ 
        vbCrLf & "OK ?" & vbCrLf & vbCrLf & _ 
        "[select NO if you want to delete just charts or just charsheets]" & vbCrLf & _ 
        "[select CANCEL if you want to stop the process and exit]", _ 
        vbQuestion & vbYesNoCancel, MsgBxTitle) 
        Select Case Rtn 
        Case vbCancel 
            Exit Sub 
        Case vbNo 
             '
             '               interact with user to clarify "No"
             '
            DelWhich = _ 
            InputBox("enter 'charts' to delete just charts" & vbCrLf & _ 
            "enter 'chartsheets' to delete just chartsheets" & vbCrLf & _ 
            "enter 'both' to delete both" & vbCrLf & vbCrLf & _ 
            "any other entry or blank or Cancel button will" & vbCrLf & _ 
            "stop the procedure without deleting anything", MsgBxTitle) 
            DelWhich = LCase(DelWhich) 
            Select Case DelWhich 
            Case "charts", "chartsheets", "both" 
            Case Else 
                Exit Sub 
            End Select 
        Case vbYes 
             
        Case Else 
            Exit Sub 
        End Select 
    End If 
     '
     '           delete any charts in worksheets
     '
    If DelWhich = "charts" Or DelWhich = "both" Then 
        For Each xlSheet In xlBook.Worksheets 
            Call xlDelCharts(xlBookName, xlSheet.Name, InformUserSheet) 
        Next xlSheet 
    End If 
     '
     '           delete any chartsheets
     '
    If DelWhich = "chartsheets" Or DelWhich = "both" Then 
        Application.DisplayAlerts = False 
        For Each xlChSheet In xlBook.Charts 
            xlChSheet.Delete 
        Next xlChSheet 
        Application.DisplayAlerts = True 
    End If 
     
    If InformUserBook = True Then 
         '
         '           inform user of results
         '
        If DelWhich = "charts" Then NumChSheets = 0 
        If DelWhich = "chartsheets" Then 
            NumSheets = 0 
            NumCharts = 0 
        End If 
        MsgBox "xlDelChartsBook" & vbCrLf & vbCrLf & _ 
        "workbook:  " & xlBookName & vbCrLf & _ 
        "# worksheets processed:  " & NumSheets & vbCrLf & _ 
        "# charts deleted:   " & NumCharts & vbCrLf & _ 
        "# chartsheets deleted:   " & NumChSheets, _ 
        vbInformation, MsgBxTitle 
    End If 
    Exit Sub 
     '
     '           error handling
     '
xlBookError: 
    MsgBox "xlDelChartsBook: workbook specified is " & xlBookName & vbCrLf & _ 
    "That workbook is not presently open" & vbCrLf & vbCrLf & _ 
    "No actions taken", vbCritical, MsgBxTitle 
    Exit Sub 
     
ErrorHandling: 
    MsgBox "xlDelChartsBook: error encountered; err = " & Str(Err), vbCritical, _ 
    MsgBxTitle 
     
End Sub

How to use:

  1. Copy the above code.
  2. Open any workbook.
  3. Press Alt + F11 to open the Visual Basic Editor (VBE).
  4. In the left side window, hi-lite the target spreadsheet [it will likely be called VBAProject(filename.xls) where filename is the name of the spreadsheet]
  5. Select an existing code module for the target worksheet; or from the Insert Menu, choose Insert | Module.
  6. Paste the code into the right-hand code window.
  7. Close the VBE, save the file if desired.
  8. See ?Test The Code? below
 

Test the code:

  1. Open the example
  2. The example spreadsheet contains two worksheets with several charts in each, one worksheet with no charts, and one chartsheet
  3. Each worksheet or chartsheet has two yellow textboxes linked to the relevant procedures.
  4. NOTE that both xlDelCharts and xlDelChartsBook have optional arguments to identify the target worksheet or workbook and control if information is displayed to the user regarding status. The defaults are set to what makes the most sense in most cases, but specific use will suggest appropriate values for these arguments.
 

Sample File:

xlDelCharts.zip 19.89KB 

Approved by mdmackillop


This entry has been viewed 183 times.

Please read our Legal Information and Privacy Policy
Copyright @2004 - 2020 VBA Express