none
a vba chart to close out once viewed RRS feed

  • Question

  • I have a vba chart that I want to close once viewed. As now the chart is displayed on the sheet, no problem, but now what. How can I add a msgbox to delete the chart from the sheet. I don't need to save the chart at all.

    Jim Neely

    Friday, August 24, 2012 8:13 PM

Answers

  • Adapt the following to your needs

    Sub Test()
    Dim msgRes As VbMsgBoxResult
    Dim sPrompt As String, sTitle As String
            sPrompt = "Are you done with the Chart and want to delete it ?"
         sTitle = "my Chart App"
            msgRes = MsgBox(sPrompt, vbYesNo Or vbQuestion, sTitle)
            If msgRes = vbYes Then
                 '                ActiveChart.Delete        ' will error if there's not an 
    activechart
                 '                ActiveSheet.ChartObjects.Delete        ' all charts on the 
    sheet
                    ' the topmost chart, typically the last added
    Dim idx As Long        ' declare up top
                 idx = ActiveSheet.ChartObjects.Count
                 If idx > 0 Then
                         ActiveSheet.ChartObjects(idx).Delete
                 End If
         Else
                 ' user clicked No or Esc
         End If
            sPrompt = "press OK to delete the chart"
         msgRes = MsgBox(sPrompt, vbOKCancel Or vbQuestion, sTitle)
         If msgRes = vbOK Then
                 ' user pressed OK
         Else
                 'user cancelled
         End If
    End Sub

    As you can see you could use Yes/No or OK/Cancel

    Not sure if you want to delete the activechart, the last one added, or all charts on the sheet, adapt as indicated.

    Peter Thornton

    • Marked as answer by STARFALLS Saturday, October 27, 2012 8:37 AM
    Monday, August 27, 2012 8:52 PM
    Moderator
  • Try these in the Thisworkbook module

    If you want all charts on shete1 to be deleted when you save the workbook or deactivate the sheet try these in the Thisworkbook module

    Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _
                                                                                 Cancel As Boolean)
         ThisWorkbook.Worksheets("Sheet1").ChartObjects.Delete
    End Sub
    
    Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
    Dim bSaved As Boolean
         If Sh.Name = "Sheet1" Then
                 bSaved = ThisWorkbook.Saved
                 Sh.ChartObjects.Delete
                 ThisWorkbook.Saved = bSaved
         End If
    End Sub

    If you only want to delete one particular chart on the sheet post back if not sure how to adapt

    Concerning the msgbox, are you asking how the chart can be deleted if the user say clicks Yes to a question in a msgbox asking if the chart(s) should be deleted?

    Peter Thornton

    • Marked as answer by STARFALLS Saturday, October 27, 2012 8:37 AM
    Friday, August 24, 2012 10:49 PM
    Moderator
  • So how do you know when the user has finished viewing the chart, and what was that msgbox all about (in your OP) and how does it get triggered.

    Peter Thornton

    • Marked as answer by STARFALLS Saturday, October 27, 2012 8:37 AM
    Monday, August 27, 2012 9:11 AM
    Moderator
  • I don't understand the problem. Why not simply delete the chart after the msgbox is dismissed, or if the msgbox asks the user something like "Click Yes to delete the chart if done" then only delete if the user clicked Yes.

    Clarify if the problem is how to trap if Yes/No or OK/Cancel was clicked, or how to delete the chart, or something else you are not sure about.

    Peter Thornton

    • Marked as answer by STARFALLS Saturday, October 27, 2012 8:37 AM
    Monday, August 27, 2012 7:04 PM
    Moderator

All replies

  • Try these in the Thisworkbook module

    If you want all charts on shete1 to be deleted when you save the workbook or deactivate the sheet try these in the Thisworkbook module

    Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _
                                                                                 Cancel As Boolean)
         ThisWorkbook.Worksheets("Sheet1").ChartObjects.Delete
    End Sub
    
    Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
    Dim bSaved As Boolean
         If Sh.Name = "Sheet1" Then
                 bSaved = ThisWorkbook.Saved
                 Sh.ChartObjects.Delete
                 ThisWorkbook.Saved = bSaved
         End If
    End Sub

    If you only want to delete one particular chart on the sheet post back if not sure how to adapt

    Concerning the msgbox, are you asking how the chart can be deleted if the user say clicks Yes to a question in a msgbox asking if the chart(s) should be deleted?

    Peter Thornton

    • Marked as answer by STARFALLS Saturday, October 27, 2012 8:37 AM
    Friday, August 24, 2012 10:49 PM
    Moderator
  • Peter,

    Thanks for the code but I need a little more. Let me explain. I have a user that is looking at a block of data on a spreadsheet. I have a cell in front of all of this that is tied to a Macro. 'This macro will bring up a chart. Once the user is finished viewing the chart I want a way to make the chart go away.


    Jim Neely

    Sunday, August 26, 2012 8:29 PM
  • So how do you know when the user has finished viewing the chart, and what was that msgbox all about (in your OP) and how does it get triggered.

    Peter Thornton

    • Marked as answer by STARFALLS Saturday, October 27, 2012 8:37 AM
    Monday, August 27, 2012 9:11 AM
    Moderator
  • hey peter, I know the the user is finished viewing because he clicks on the msgbox or command button. this is my problem the TRIGGER. I can bring up a chart to view, but I can not make it go away. I am thinking of a msgbox or a userform to do this.

    Jim Neely

    Monday, August 27, 2012 5:43 PM
  • I don't understand the problem. Why not simply delete the chart after the msgbox is dismissed, or if the msgbox asks the user something like "Click Yes to delete the chart if done" then only delete if the user clicked Yes.

    Clarify if the problem is how to trap if Yes/No or OK/Cancel was clicked, or how to delete the chart, or something else you are not sure about.

    Peter Thornton

    • Marked as answer by STARFALLS Saturday, October 27, 2012 8:37 AM
    Monday, August 27, 2012 7:04 PM
    Moderator
  • that is what I want. a msgbox that ask the user to click Yes when finished viewing chart

    Jim Neely

    Monday, August 27, 2012 7:24 PM
  • Adapt the following to your needs

    Sub Test()
    Dim msgRes As VbMsgBoxResult
    Dim sPrompt As String, sTitle As String
            sPrompt = "Are you done with the Chart and want to delete it ?"
         sTitle = "my Chart App"
            msgRes = MsgBox(sPrompt, vbYesNo Or vbQuestion, sTitle)
            If msgRes = vbYes Then
                 '                ActiveChart.Delete        ' will error if there's not an 
    activechart
                 '                ActiveSheet.ChartObjects.Delete        ' all charts on the 
    sheet
                    ' the topmost chart, typically the last added
    Dim idx As Long        ' declare up top
                 idx = ActiveSheet.ChartObjects.Count
                 If idx > 0 Then
                         ActiveSheet.ChartObjects(idx).Delete
                 End If
         Else
                 ' user clicked No or Esc
         End If
            sPrompt = "press OK to delete the chart"
         msgRes = MsgBox(sPrompt, vbOKCancel Or vbQuestion, sTitle)
         If msgRes = vbOK Then
                 ' user pressed OK
         Else
                 'user cancelled
         End If
    End Sub

    As you can see you could use Yes/No or OK/Cancel

    Not sure if you want to delete the activechart, the last one added, or all charts on the sheet, adapt as indicated.

    Peter Thornton

    • Marked as answer by STARFALLS Saturday, October 27, 2012 8:37 AM
    Monday, August 27, 2012 8:52 PM
    Moderator