Search code examples
excelvbaimagerangeclipboard

How to prevent clipboard content from changing if selected Excel range is changed


I have a macro that copies a range of cells from Excel. The range contains a dashboard with text and conditionally formatted cells filled with color.

The user manually pastes that copied content into PowerPoint slides as images. The process is repeated several times showing different dashboard outputs.

The process was working but we added instructions within the required Excel range, next to a dropdown box, to tell users to make their selection in that cell.

We don't want the final image copy/pasted to PowerPoint file to show that instruction. Before the copy code, I added code to change the font color of the instructional text to white so that it disappears in the white background, then added code to change the font color back to grey after the copy code is executed within the macro. When the image is manually pasted after macro execution it shows the grey visible text, instead of white.

If I comment out the code that changes font color back to grey, then the resulting image doesn't show the instructional text in the image as intended because the font is white but the text needs to be changed back to grey so it is visible again.

Below is my code. Ignore Application.CopyObjectsWithCells lines that are commented out. They are just to show my attempt at an alternate solution. I tried adding a shape with the instructional text to use Application.CopyObjectsWithCells=False so the shape would not copy but it doesn't work. When resetting the property to true, the clipboard content gets cleared. Even if I don't set it to True, the shape is copy/pasted in the final image. I do want to stick with the initial approach of changing font color of the text. If that can't be done then perhaps by saving the range in a variable. How would I declare a variable which copies an Excel range that can be pasted as an image manually?

Sub Copy_DirDash()
        
    Set wsDashboard = ThisWorkbook.Sheets("Director Dashboard")
    
    'change font color of "<< make selection text" to white then copy
    wsDashboard.Range("h3").Font.Color = vbWhite
    
    DoEvents
            ' This was attempt at alternate solution. Please ignore. Currently no shapes.
            '    Application.CopyObjectsWithCells = False
            '    DoEvents
    
    wsDashboard.Range("A1").Select
    wsDashboard.Range("D2:AC26").Copy
    
    DoEvents
    
    'change font color back to grey
    wsDashboard.Range("h3").Font.Color = RGB(89, 89, 89) 'dark grey
                   
                   ' This was part of alternate solution. Ignore.
                   'Application.CopyObjectsWithCells = True
    
End Sub

Solution

  • Posting response by Tim Williams only because he gave response in comments so the checkmark is not there to mark the question as solved.

    Maybe try wsdashboard.Range("D2:AC26").CopyPicture instead? – Tim Williams