Hi,
I have a spin button that mimics a scroll bar i have in excel. It controls a specific cell in which some excel charts reference so that some sensitivity analysis can be done with visuals. The goal is to be able to do this during a presentation. Currently the macro works if I open up the powerpoint and paste with linked data from excel into the slide run the slideshow presentation. However, after I save and close the powerpoint, the next time I open up the file and run the slideshow, the charts do not update during the slideshow. I have verified that the button still updates the charts in excel, it just does not reflect the change until i press the "refresh data" button under chart options.
Here is a copy of my code:
Private Sub SpinButton1_Change()
Dim xlApp As Object, wb As Object, ws As Object, shp As Shape
On Error Resume Next
Set xlApp = GetObject(, "Excel.Application")
On Error GoTo 0
If xlApp Is Nothing Then
Set xlApp = CreateObject("Excel.Application")
End If
On Error Resume Next
Set wb = xlApp.Workbooks("File Name")
On Error GoTo 0
If wb Is Nothing Then Set wb = xlApp.Workbooks.Open("File Path")
Set ws = wb.Worksheets("Channel Savings")
ws.[H5] = SpinButton1.Value
DepositBox.Value = SpinButton1.Value
ws.Calculate
For Each shp In Me.Shapes
If shp.Type = msoLinkedOLEObject Then
shp.LinkFormat.Update
End If
Next shp
Set xlApp = Nothing
Set wb = Nothing
Set wc = Nothing
Set shp = Nothing
End Sub
One suggestion I've heard is to go to edit links to files and set it to automatic. Unfortunately this has not worked either.
Thanks in advance for any help
Bookmarks