linked image not refreshing

[rclark]

I have a named range on sheet1 that I "copied as picture" into another sheet. I then click on the image and put the name of the range in the formula bar so the image will update as changes occur on sheet 1. I do this a lot and I know it's pretty common but I'm finding that, on certain computers when I run a macro to take some images of the sheet, the picture goes blank so the images I captured show blank, and the sheet that was previously updating the image with no issue has gone blank as well and stays blank until I do something that triggers the display to refresh like enter a value in a cell and click enter. The macro has no weird events or updating going on and this works on most computers I've tested with. I can't find a common theme of Windows or Excel version that points to a setup issue. Is there a global "refresh" that I can do to force this to all refresh before running the macro? As I said, right before I run the macro everything is as it should be with regards to updating and displays. I've tried ScreenUpdating, EnableEvents, and DoEvents and none of them change the result.

 

[John_w]

How is the macro saving the sheet as an image? If it's using a temporary ChartObject, a blank image is a common problem in Excel 2016. The solution is to Activate the temporary ChartObject.

Post your code if you need more help.

 

[rclark]

Here is the code. The first code preps the wb for changes. The second does the copy/paste:

Call SetGlobals

On Error Resume Next
sSht.ShowAllData
Application.ScreenUpdating = False
ThisWorkbook.Unprotect Password:=rPW
sSht.Unprotect Password:=rPW
cSht.Unprotect Password:=rPW
vSht.Unprotect Password:=rPW
hSht.Unprotect Password:=rPW
Dim ws As Worksheet
    For Each ws In ActiveWorkbook.Worksheets
        ws.Visible = xlSheetVisible
    Next ws

Private Sub ReviseDocSnapShots()

Dim PicTop As Integer

' Delete Current release image from sheet
        On Error Resume Next
        For Each sh In cSht.Shapes
            sh.Delete
        Next sh

' Get the bottom of the lowest pic in history sheet
        PicTop = 0
        For Each sh In hSht.Shapes
            If sh.Top + sh.Height > PicTop Then
                PicTop = sh.Top + sh.Height
            End If
        Next sh

' Copy snapshot of release into current and history sheets
    sSht.Select
    rPrint.Select  [COLOR=#FF0000]' This is the range I'm copying.  It has a linked image that is displaying contents from another sheet within the range.  When I start the macro the linked image is displaying properly[/COLOR]
    Selection.CopyPicture Appearance:=xlScreen, Format:=xlPicture
    ' Paste in History
    hSht.Activate
    Range("A1").Select
    ActiveSheet.Paste [COLOR=#FF0000]' The contents of the linked image is blank but everything else pasted fine[/COLOR]
    With Selection
        .Name = "Rev_" & rRevNo.Value
        .Top = PicTop
    End With
    ' Paste in Current
    cSht.Activate
    Range("A1").Select
    ActiveSheet.Paste ' Same here, no linked image but the rest pasted fine

sSht.Select 

End Sub
[COLOR=#FF0000]' Now when it goes back to the range I copied the linked range is blank until I edit a cell, then it refreshes[/COLOR]

 

[John_w]

I've tried to follow your posts and reproduce the issue with your code, but it seems to work for me.

I can only suggest pausing the macro for a few milliseconds with the Sleep API and calling DoEvents at various points.

Add this at the top of the 2nd module, above any procedures:

#If VBA7 Then
    Public Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=Else]#Else[/URL] 
    Public Declare Sub Sleep Lib "kernel32" (ByVal milliseconds As Long)
[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=End]#End[/URL]  If

Call the Sleep function with the number of milliseconds like this:

Sleep 500
DoEvents