Linked picture update from VBA

[Michael van den Berg]

Hey all,

I was using the dynamic graphs through linked pictures from Chandoo succesfully for a couple of months in combination with VBA. However, now i found a hick-up i am unable to solve myself (and google).

In order to keep my excel file fast i change the linked picture formulae to "" when deactivating the sheets and activating them again when the sheets get activated (through the worksheet_activate and deactivate in the worksheet modules).

However this is not working when i do it through a general module (so not visiting the sheets myself). The linked pictures do get the formula in VBA, but only get updated when manually activating the worksheets after i run my vba code.

I tried updating through application.refreshall and .calculatefullrebuild and even running sheets.activate as a dirty solution, but even this didnt update the sheet.

So is there a way to update or refresh the linked picture after setting the formula through VBA ?

Sub PrintButton_Click()
Application.ScreenUpdating = False

  If Application.Dialogs(xlDialogPrinterSetup).Show = False Then
  MsgBox "Geannuleerd"
  Exit Sub
  End If
  

  Dim intCounter As Integer
  
  Dim StrPic As String
  Dim StrFor As String
  Dim intPicture As Integer
  
  Dim StrKolom As String
  Dim RanKolom As Range
  Dim ranCel As Range
  
  Dim RanVink As Range
  Dim RanVinkCel As Range
  
  For intCounter = 5 To 11
  
  If Range("VinkKPI").Value = 1 Then
  
  For intPicture = 1 To 6
  StrFor = "Grafiek_Select" & intPicture
  StrPic = "Grafiek" & intPicture
  
  With Sheets(intCounter).Pictures(StrPic)
  .Formula = StrFor 'This sets the formula for the linked picture and it does this, i checked
  .PrintObject = msoTrue
  End With
  
  Next
  
  Else
  
  For intPicture = 1 To 6
  StrPic = "Grafiek" & intPicture
  Sheets(intCounter).Pictures(StrPic).PrintObject = msoFalse
  Next
  
  End If
  
  Next

  Sheets(3).Select True

  For intCounter = 4 To 12
  
  If Sheets(2).Range("D" & intCounter).Value = 1 Then
  With Sheets(intCounter)
  .Select False
  .PageSetup.CenterFooter = "&A"
  .PageSetup.RightFooter = "&P of &N"
  End With
  End If
  
  Next
  
  ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, IgnorePrintAreas:=False
  
'reset settings
  For intCounter = 5 To 11
  
  For intPicture = 1 To 6
  StrPic = "Grafiek" & intPicture
  
  With Sheets(intCounter).Pictures(StrPic)
  .Formula = ""
  .PrintObject = msoTrue
  End With

  Next
  
  Next

  Sheets(2).Select True
  
  Application.ScreenUpdating = True
End Sub

 

[Michael van den Berg]

Its not updating because screenupdating is set to false.
I put it back to true before printing to update the sheets and then put it back to false after i printed it.

Although it does everything correctly now, the code after printing becomes very slow, although i put screenupdating back to false...

Before i put the additional screenupdating code it ran super fast after printing

Application.ScreenUpdating = True
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, IgnorePrintAreas:=False
Application.ScreenUpdating = False

'rest of code becomes slow here