Results 1 to 5 of 5
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I am working with someone else here on an automation problem. he has developed an Excel spreadsheet that automatically creates sets of charts on various sheets. he would like to be able to detect whether there exists any charts on any given sheet so he can delete these charts programmatically. I have been looking into it, but thus far a method eludes me. What I would like to be able to do is,

    Find and count any existing charts on a worksheet --> that way, a dynamic delete can be built.

    TIA!

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    You could use ActiveSheet.Chartobjects.count
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts
    thanks - that should probably do it.

  4. #4
    New Lounger
    Join Date
    May 2010
    Posts
    10
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi Steve,

    The following VBA should get you started - it includes a loop to examine all worksheets:

    Sub chart_killer()

    Dim n_charts As Long, k_chart As Long
    Dim usr_msg As VbMsgBoxResult
    Dim wk_sheet As Worksheet

    For Each wk_sheet In ThisWorkbook.Sheets
    n_charts = wk_sheet.ChartObjects.Count
    If n_charts > 0 Then
    wk_sheet.Activate
    For k_chart = n_charts To 1 Step -1
    wk_sheet.ChartObjects(k_chart).Activate
    usr_msg = MsgBox("Delete the selected chart?", vbQuestion + vbYesNo, _
    "Delete")
    If usr_msg = vbYes Then wk_sheet.ChartObjects(k_chart).Delete
    Next k_chart
    End If
    Next wk_sheet

    End Sub

    Note: Activate rather than Select
    Hope this helps,

    RAK.

  5. #5
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts
    we went with this one-liner:

    ActiveSheet.ChartObjects.Delete

    as there's no need to know how many charts are to be deleted, just delete.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •