linked image not refreshing

rclark

New Member
Joined
Jun 24, 2015
Messages
27
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.
 

Some videos you may like

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

John_w

MrExcel MVP
Joined
Oct 15, 2007
Messages
6,412
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

New Member
Joined
Jun 24, 2015
Messages
27
Here is the code. The first code preps the wb for changes. The second does the copy/paste:

Code:
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




Code:
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

MrExcel MVP
Joined
Oct 15, 2007
Messages
6,412
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:

Code:
#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:
Code:
Sleep 500
DoEvents
 

Watch MrExcel Video

Forum statistics

Threads
1,108,627
Messages
5,523,985
Members
409,551
Latest member
WillCaton

This Week's Hot Topics

Top