Adding Picture to Userform from Sheet on initialize

Question

I'm trying to write a script that will allow me to load pictures contained in my workbook into my userform dynamically in an attempt to make the workbook completely portable. I've come up with the following that seems to work but there is one line which I don't understand why it doesn't work without. If I remove the line .ChartArea.Select the image won't load. However, If I leave it in it works fine. Ideally I'd like to remove it so I can avoid using a pointless Select. Can anyone explain?

Option Explicit

Private Sub UserForm_Initialize()
    Me.Picture = LoadPicture(Filename:=ExportMyPicture(Sheet1.Pictures(1)))
    Me.PictureSizeMode = fmPictureSizeModeZoom
End Sub


Private Function ExportMyPicture(pic As Picture) As String
    Dim fName As String

    fName = Environ("Temp") & "/" & pic.Name & ".bmp"

    With pic.Parent.ChartObjects.Add(50, 40, pic.ShapeRange.Width, pic.ShapeRange.Height)
        .Border.LineStyle = 0

        pic.Copy

        With .Chart
            ' Removing the following line stops the picture from loading
            .ChartArea.Select
            .Paste
            If .Export(Filename:=fName, filtername:="bmp") Then
                ExportMyPicture = fName
            End If
        End With
        .Delete
    End With
End Function

Demo:

Using this png: url: SO converts it to a jpg http://pngimg.com/uploads/cat/cat_PNG50497.png

Picture by Mikku

@Mikku I get an 'Object doesn't support this property or method' error using that — Tom
– Tom, Commented Jul 25, 2019 at 17:16
stackoverflow.com/questions/10782394/pop-up-the-excel-statusbar/… — Siddharth Rout
– Siddharth Rout, Commented Jul 25, 2019 at 18:44
See the above link. I have used the pie images from the sheet and shown them in the userform... — Siddharth Rout
– Siddharth Rout, Commented Jul 25, 2019 at 18:45
@SiddharthRout looks good but unfortunately I cant use ‘user32’ api calls. My IT department have blocked them. — Tom
– Tom, Commented Jul 25, 2019 at 18:53

Mathieu Guindon · Accepted Answer · 2019-07-25 19:55:02Z

1

It has all looks of a timing issue, which could be a bug in how the OLE object is implementing its .Copy method; the .Select call gives it the kick it needs to get back on track.

Comments are there to say why we do things. This is one of these cases where commenting is simply the best possible thing to do... your comment isn't bad at all - it explains why, not what - and that is exactly what we want comments to say.

' Removing the following line stops the picture from loading
.ChartArea.Select

Some alternatives:

.ChartArea.Select ' Picture.Copy timing issue; this prevents subsequent .Paste from being no-op.

.ChartArea.Select ' HERE BE DRAGONS! Remove this instruction and you'll break the .Paste!

answered Jul 25, 2019 at 19:55

Mathieu Guindon

71.5k8 gold badges113 silver badges250 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Tom Over a year ago

I've accepted this as an answer as it is probably the closest to poking at the root cause of it. Bit frustrating but at least it works overall. Thanks :)

user6585761 · Accepted Answer · 2019-07-25 18:49:37Z

It looks like it may be a timing issue. If you pause the macro for a few seconds after copying the picture to the clipboard, it creates a file with the image and loads it successfully. However, .ChartArea.Select seems to be a good workaround. In any case, if you want to try pausing the macro, here's an example...

Option Explicit

Private Sub UserForm_Initialize()
    Me.Picture = LoadPicture(Filename:=ExportMyPicture(Sheet1.Pictures(1)))
    Me.PictureSizeMode = fmPictureSizeModeZoom
End Sub

Private Function ExportMyPicture(pic As Picture) As String
    Dim fName As String

    fName = Environ("Temp") & "/" & pic.Name & ".bmp"

    With pic.Parent.ChartObjects.Add(50, 40, pic.ShapeRange.Width, pic.ShapeRange.Height)
        .Border.LineStyle = 0

        pic.Copy

        PauseMacro

        With .Chart
            .Paste
            If .Export(Filename:=fName, filtername:="bmp") Then
                ExportMyPicture = fName
            End If
        End With
        .Delete
    End With
End Function

Private Sub PauseMacro()

    Dim StartTime As Single

    StartTime = Timer
    Do Until Timer > StartTime + 3 'seconds delay
        DoEvents
    Loop

End Sub

Note that a 1 second delay seems to work as well, but maybe best to keep it at a 3 second delay just in case.

Collectives™ on Stack Overflow

Adding Picture to Userform from Sheet on initialize

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related