Background Image Scale

[mvpalley]

Hey, I am getting this highlight when I am trying to debug. I am trying to get the background image to stay set in relation to the cells (no matter what size screen it is on). What does it mean and how do I fix it? I really appreciate the help (I am new to VBA...).

Matt

 

[ranman256]

if you mean the code STOPS at this line and you did not put a stop point, its a 'ghost stop point'.
VB doesn't know it but the stop point is gone, so just press F5 to continue.

But if you meant to stop the code here, then it's the assignement. You may have the wrong name of he shape.

 

[mvpalley]

I think I initially phrased my question a bit weirdly. My goal is to have the background image locked into the same cell area regardless of what screen you are on. When I copied the vba code from a thread I had on here, the background image is still changing size in relation to the cells. Do you have to save and close the module and the file and come back in for it to start working? How do I know the VBA code is flowing through and active? I appreciate the help!!

 

[tonyyy]

Matt,

As @ranman256 has pointed out..."You may have the wrong name of the shape."

If you copy/pasted an image onto your sheet, excel will not retain the original image name. To identify it's "new" name, click the image then look in the Name Box in the upper left corner of the sheet. Most likely it'll be something like "Picture 1".

Cheers,

Tony

 

[mvpalley]

The image is a background. I am not sure how to find it's "new" name given that I can't click on it

 

[tonyyy]

When you use the Page Layout / Background option, the image is tiled and covers the entire sheet. You could edit the image before hand to get a better-fit, but it'll remain static.

Instead of a background image, another option is to draw a Rectangle shape over your data. Then with the Format Shape settings, set the Fill to a picture and select your image. Then set the Transparency level so that your data appears through the image. Use the code you pasted to size the rectangle whenever the sheet is activated. (The "new" name will be something like "Rectangle 1".)

 

[mvpalley]

Thanks for the rectangle shape option. With this, however, are you not able to alter the cells behind the photo without moving the rectangle first? See screenshot below. I am trying to be able to readily add the purple and green cell filled colors over the photo. Thanks for all of your help!

 

[tonyyy]

Well, you could use the arrow keys to navigate to the cells you want to alter; not the most convenient but doable.

Excel/vba doesn't have a direct function to scale a background image, let alone resize a pic. You can, however, utilize a chart object as a workaround - (For some detail you can view the post at TheSpreadsheetGuru.) - which is what the following code does.

Private Sub Worksheet_Activate()
Dim cht As ChartObject
Dim ActiveShape As Shape
Dim UserSelection As Variant

ActiveSheet.SetBackgroundPicture filename:=""
ActiveSheet.Shapes.AddShape(msoShapeRectangle, _
    Range("A1").Top, _
    Range("A1").Left, _
    Range("A1:FR1").Width, _
    Range("A1:A52").Height).Select

With Selection.ShapeRange
    .Fill.Visible = msoTrue
    '''' Add your file path and name below
    .Fill.UserPicture "C:\Docs 2021\2021 Miscellaneous\resized.jpg"
    .Line.Visible = msoFalse
End With

Set UserSelection = ActiveWindow.Selection
Set ActiveShape = ActiveSheet.Shapes(UserSelection.Name)

'Create a temporary chart object (same size as shape)
Set cht = ActiveSheet.ChartObjects.Add( _
    Left:=ActiveCell.Left, _
    Width:=ActiveShape.Width, _
    Top:=ActiveCell.Top, _
    Height:=ActiveShape.Height)

'Format temporary chart to have a transparent background
    cht.ShapeRange.Fill.Visible = msoFalse
    cht.ShapeRange.Line.Visible = msoFalse
    
'Copy/Paste Shape inside temporary chart
    ActiveShape.Copy
    cht.Activate
    ActiveChart.Paste
  
'Save chart
    ''''Add your file path and name below
    cht.Chart.Export "C:\Docs 2021\2021 Miscellaneous\resized.jpg"
'Delete temporary Chart
    cht.Delete
'Delete active shape
    ActiveShape.Delete
'Insert background image
    ''''Add your file path and name below
    ActiveSheet.SetBackgroundPicture filename:= _
        "C:\Docs 2021\2021 Miscellaneous\resized.jpg"

End Sub

Be sure to modify the file path and name of your image.