CODEWITHSUNDEEP
excelvba
Tom
Tom

Reputation: 9878

Adding Picture to Userform from Sheet on initialize

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:

Demo

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

Picture by Mikku

enter image description here

Upvotes: 1

Views: 1408

Answers (2)

Mathieu Guindon
Mathieu Guindon

Reputation: 71187

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!

Upvotes: 1

Domenic
Domenic

Reputation: 8114

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.

Upvotes: 1

Related Questions