none
How Could I register to Excel Cell change event for the Worksheet underlying Chart in PowerPoint. RRS feed

  • Question

  • I have been trying to register to Excel Cell change event for the Worksheet underlying Chart in PowerPoint,but I am not getting the events constantly.

    I want to get the event whenever user changes the cells in the Data Sheet of a Chart.

    Following is my code:

    I have registered for WindowSelectionChange Event for PowerPoint Application and registering to Excel cell change inside it,but the issue is Sheet is opened as soon as I right click on the Chart  and also the events are not getting registered.

    If I don't use pChartData.Activate,I get exception.

    Private Sub app_WindowSelectionChange(Sel As PowerPoint.Selection) Handles app.WindowSelectionChange
            Dim shpRange As PowerPoint.ShapeRange = Nothing
            'Dim eWorkbook As Excel.Workbook = Nothing
            'Dim eWorksheet As Excel.Worksheet = Nothing
    
            If Not Sel.Type = 0 Then
                shpRange = Sel.ShapeRange
            End If
            Try
                If Not (shpRange Is Nothing) Then
                    For Each shp As PowerPoint.Shape In shpRange
                        If shp.HasChart = Microsoft.Office.Core.MsoTriState.msoTrue Then
                            pChart = shp.Chart
    
                            Dim pChartData As PowerPoint.ChartData = pChart.ChartData
                            If Not (pChartData Is Nothing) Then
    
                                pChartData.Activate()
                                eWorkbook = pChartData.Workbook
                                eWorksheet = eWorkbook.Worksheets(1)
                                EventDel_CellsChange = New Excel.DocEvents_ChangeEventHandler(AddressOf CellsChange)
                                AddHandler eWorksheet.Change, EventDel_CellsChange
                                eWorkbook.Close()
                                Exit For
    
                            End If
    
                        End If
                    Next
                End If
            Catch
    
            End Try
            ribbon.Invalidate()
            
    End Sub

    Please help me,how could I achieve the same.
            


    Point5Nyble


    • Edited by Point5Nyble Tuesday, October 13, 2015 10:25 AM Updated Code
    Tuesday, October 13, 2015 9:55 AM

All replies

  • Hi abhinovpankaj,

    Why did you add this code “eWorkbook.Close()”? I suggest you comment out this, and try it again.

    If it did not work, I suggest you try the code below.

    Imports Microsoft.Office.Interop
    
    Public Class ThisAddIn
        Private Sub ThisAddIn_Startup() Handles Me.Startup
    
        End Sub
    
        Private Sub ThisAddIn_Shutdown() Handles Me.Shutdown
    
        End Sub
        Private Sub app_WindowSelectionChange(Sel As PowerPoint.Selection) Handles Application.WindowSelectionChange
            Dim shpRange As PowerPoint.ShapeRange = Nothing
            Dim eWorkbook As Excel.Workbook = Nothing
            Dim eWorksheet As Excel.Worksheet = Nothing
            Dim pChart As PowerPoint.Chart
    
            If Not Sel.Type = 0 Then
                shpRange = Sel.ShapeRange
            End If
            Try
                If Not (shpRange Is Nothing) Then
                    For Each shp As PowerPoint.Shape In shpRange
                        If shp.HasChart = Microsoft.Office.Core.MsoTriState.msoTrue Then
                            pChart = shp.Chart
    
                            Dim pChartData As PowerPoint.ChartData = pChart.ChartData
                            If Not (pChartData Is Nothing) Then
                                pChartData.Activate()
                                eWorkbook = pChartData.Workbook
                                eWorksheet = eWorkbook.Worksheets(1)
                                AddHandler eWorksheet.Change, AddressOf Worksheet_Change
                                Exit For
    
                            End If
    
                        End If
                    Next
                End If
            Catch
            End Try
        End Sub
    
        Private Sub Worksheet_Change(ByVal Target As Excel.Range)
            MsgBox("t")
        End Sub
    
    
    End Class
    

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Wednesday, October 14, 2015 10:20 AM
  • Yes,I have tried that already but the event is fired only for the first cell change.

    Suppose again I modify some other cell,event is not received by the event handler.


    Point5Nyble

    Friday, October 16, 2015 4:55 AM
  • Hi abhinovpankaj,

    With my code, I failed to reproduce your issue.

    It would be helpful if you could share us your code and steps to reproduce your issue.

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Monday, October 19, 2015 7:58 AM
  • I have already shared the code of WindowSelectionchange event handler.

    Steps:

    1.Create a graph in Powerpoint

    2.Close the excel which pops when a graph is added .

    3.Reopen the datasheet,using right click->edit data,the sheet is opened as soon as you click on the graph.

    4.Now modify the data,event may be raised

    5.Again edit some cell,without closing the file,cell change event is not fired.

    Application:Office 2010,PowerPoint


    Point5Nyble

    Monday, October 19, 2015 8:52 AM
  • Hi abhinovpankaj,

    With your steps, I think you would not achieve your requirement. After you reopen the datasheet with right click-> edit data, your event would not be added to the datasheet. Your event was added while app_WindowSelectionChange fired, but it would be remove if you close this workbook.

    For a work way, I suggest you try my code which did not close the workbook.
    Steps:
    1. Change a selection from a shape to chart, the excel workbook would be opened
    2. Modify the cells which you want, and the event would fire every time.

    Note, if you close this workbook, and manually open the workbook with right click, it would not work.

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Monday, October 19, 2015 9:22 AM
  • I understand this,but whenever you open the Workbook by right click,the WindowSelectionChange event is fired even if shape is selected or chart is being selected.So I am expecting the cellchange event to be registered and Yes,I have removed the line which closed the Excel Application.Still the event is not consistent.

    Also,as soon as I right click on chart/shape ,the Workbook opens which causes the right click menu to close,which is not user friendly.


    Point5Nyble

    Monday, October 19, 2015 11:26 AM
  • Hi Point5Nyble,

    >> whenever you open the Workbook by right click,the WindowSelectionChange event is fired even if shape is selected or chart is being selected

    Yes, you are right, when you select a shape or chart, WindowSelectionChange would be fired. So you could register the event. But after you close the workbook, and before right click the chart, you need to be sure the selection has been changed.

    >> as soon as I right click on chart/shape ,the Workbook opens which causes the right click menu to close,which is not user friendly

    This was caused by “pChartData.Activate()”, you could remove it.

    Here is a demo code:

        Private Sub app_WindowSelectionChange(Sel As PowerPoint.Selection) Handles Application.WindowSelectionChange
            Dim shpRange As PowerPoint.ShapeRange = Nothing
            Dim eWorkbook As Excel.Workbook = Nothing
            Dim eWorksheet As Excel.Worksheet = Nothing
            Dim pChart As PowerPoint.Chart
    
            If Not Sel.Type = 0 Then
                shpRange = Sel.ShapeRange
            End If
            Try
                If Not (shpRange Is Nothing) Then
                    For Each shp As PowerPoint.Shape In shpRange
                        If shp.HasChart = Microsoft.Office.Core.MsoTriState.msoTrue Then
                            pChart = shp.Chart
    
                            Dim pChartData As PowerPoint.ChartData = pChart.ChartData
                            If Not (pChartData Is Nothing) Then
                                'pChartData.Activate()
                                eWorkbook = pChartData.Workbook
                                eWorksheet = eWorkbook.Worksheets(1)
                                RemoveHandler eWorksheet.Change, AddressOf Worksheet_Change
                                AddHandler eWorksheet.Change, AddressOf Worksheet_Change
                                Exit For
    
                            End If
    
                        End If
                    Next
                End If
            Catch
            End Try
        End Sub
    
        Private Sub Worksheet_Change(ByVal Target As Excel.Range)
            MsgBox("t")
        End Sub
    

    Note, this would not work if you close the workbook, and the selection did not change.

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Tuesday, October 20, 2015 3:22 AM
  • Hi,

    If I comment pChartData.Activate then a blank Excel Application is opened and exception is thrown "Error HRESULT E_FAIL has been returned from a call to a COM component"

    So I guess we will have to live with this opening and closing of excel data sheet.



    Point5Nyble

    Thursday, October 22, 2015 4:54 AM
  • Hi Point5Nyble,

    Did you make any other modify? Have you tried my code? I did not get this error. What your Office version and project template?

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Friday, October 23, 2015 7:23 AM
  • Yes..I used your code without any change..I am using Office 2010 32 bit.

    The project template is Office 2013.


    Point5Nyble

    Saturday, October 24, 2015 2:21 PM
  • Would you like to see the Solution..I guess there is something wrong in my whole code which may be causing the issue.

    Point5Nyble

    Monday, October 26, 2015 4:54 AM
  • Hi Point5Nyble,

    To check whether there is some other code which caused this issue, I suggest you create a new add-in for PowerPoint with this simple code.

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Monday, October 26, 2015 8:48 AM
  • I will try this..

    Just for info ..I have added my custom button in Ribbon( using xml).

    And the app variable is initialized when user clicks on the ribbon button.

    Also this whole Window selection code has been written in the same cs file.


    Point5Nyble

    Monday, October 26, 2015 9:30 AM
  • Hi Point5Nyble,

    What is the result for your try? If your issue has not been resolved, please feel free to let us know your current situation.

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Wednesday, October 28, 2015 7:15 AM
  • Sorry,I have been busy in some other stuff..I will share the results soon..

    >>"Just for info ..I have added my custom button in Ribbon( using xml).

    And the app variable is initialized when user clicks on the ribbon button.

    Also this whole Window selection code has been written in the same cs file."

    Will this have effect on events generated??


    Point5Nyble

    Wednesday, October 28, 2015 8:40 AM
  • I tried this on both PowerPoint 2013 and 2010.

    On PowerPoint 2013,events are generated fine (which is same case with my AddIn code too) without  this line of code.“pChartData.Activate()”,

    but for PowerPoint  2010,Exception is thrown if “pChartData.Activate()” line is commented .

    Any thoughts on this?


    Point5Nyble

    Wednesday, October 28, 2015 6:20 PM
  • Hi Point5Nyble,

    Did you mean it worked correctly under PowerPoint 2013? If so, I suggest you update your PowerPoint 2010 to 2013.

    If you did not want to update your PowerPoint, with “pChartData.Activate()”, did you code work?

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Thursday, October 29, 2015 2:57 AM
  • For 2010 ,code failed with the exception ..as I replied .

    It works fine if there is pchartdat.activate,but again with this..the events don't fire properly..


    Point5Nyble

    Thursday, October 29, 2015 3:38 AM
  • I was thinking of handling SheetChange Adding VBA code using workbook.VBProject.VBComponents.Add

    https://support.microsoft.com/en-us/kb/303872

    Could this be a feasible solution?


    Point5Nyble

    Friday, October 30, 2015 8:45 AM
  • Hi,

    Still I am facing the issue..If you could help me..

    I made an observation that when pchartdat.activate is called, a new excel process is created,which is not being terminated/existed even when the Excel data sheet is closed.

    So,when I manually go to task manager and kill that Excel process,and then the event is registered as per the above mentioned code,it works fine..

    Could you please let me know what am I missing?

    Public Function ConvertoWaterFall() As String
    
    
            errorMessage = ""
            Try
                
                Dim sel As PowerPoint.Selection = Globals.ThisAddIn.Application.ActiveWindow.Selection
                If Not (sel Is Nothing) Then
    
                    For Each shp As PowerPoint.Shape In sel.ShapeRange
                        If shp.HasChart = Microsoft.Office.Core.MsoTriState.msoTrue Then
                            pChart = shp.Chart
                            If pChart.ChartType = Microsoft.Office.Core.XlChartType.xlColumnStacked Then
                                Dim pChartData As PowerPoint.ChartData = pChart.ChartData
                                hasCorrectChart = True
                                pChartData.Activate()
                                eWorkbook = pChartData.Workbook
    
                                'eWorkbook.Application.ScreenUpdating = False
                                eWorksheet = eWorkbook.Worksheets(1)
                                'eWorksheet1 = eWorkbook.Worksheets(2)
                                eWorksheet.Range("B8").Delete(Excel.XlDeleteShiftDirection.xlShiftToLeft)
    
                                iRowCount = eWorksheet.UsedRange.Rows.Count
                                iColumnCount = eWorksheet.UsedRange.Columns.Count
    
                                Try
                                    Dim resp As String = System.Windows.Forms.MessageBox.Show("Do you want to create a duplicate Copy of the Selected Slide", "Create Copy", Windows.Forms.MessageBoxButtons.YesNo, Windows.Forms.MessageBoxIcon.Question, Windows.Forms.MessageBoxDefaultButton.Button1, Windows.Forms.MessageBoxOptions.DefaultDesktopOnly, False)
                                    If resp = vbYes Then
                                        CreateCopyofSlide(sel)
                                    End If
                                    'calculationAndFormatting(False, eWorksheet)
                                    calculationAndFormatting2(False, eWorksheet)
    
                                    addCustomData(shp)
    
                                    'eWorkbook.Close()
                                    'eWorkbook.Application.Quit()
    
    
                                Catch
    
                                    deleteDuplicateCopy(sel)
                                    System.Windows.Forms.MessageBox.Show("Transform Failed")
                                    'eWorkbook.Close()
                                    'eWorkbook.Application.Quit()
                                End Try
                            Else
                                errorMessage = errorMessage + pChart.Name.ToString() + " cannot be converted to Waterfall,not a valid chart type for conversion" & vbCr & vbLf
    
                            End If
    
                        End If
                    Next
                End If
                If errorMessage.Length > 0 Then
    
                ElseIf hasCorrectChart = False Then
                    errorMessage = "Select Appropriate Charts to transform to waterfall"
                End If
            Catch ex As Exception
                errorMessage = "Select Appropriate Charts to transform to waterfall"
            End Try
            eWorksheet = Nothing
            eWorkbook = Nothing
           
            GC.Collect()
            GC.WaitForPendingFinalizers()
            GC.Collect()
            GC.WaitForPendingFinalizers()
            Return errorMessage
        End Function


    Point5Nyble

    Tuesday, November 3, 2015 4:56 PM
  • Hi Point5Nyble,

    Sorry for the delay.

    >> I made an observation that when  pchartdat.activate is called, a new excel process is created,which is not being terminated/existed even when the Excel data sheet is closed.

    Which version did this issue happened? Did the code work correctly under PowerPoint 2013? If it could, I suggest you update your PowerPoint to 2013.

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Friday, November 6, 2015 5:06 AM
  • This COM Addin needs to run on all the Powerpoint versions 2007,2010 and 2013,upgrading to 2013 is not an option for me..:(

    Point5Nyble

    Friday, November 6, 2015 5:43 AM