Detect when Image in Shape appears

[ptspawn]

Hi everyone,

First of all, great site. I've had sooo many questions answered here that I can't thank enough.
But, for the first time after looking everywhere, there is one i can't seem to have answered.

I'm an engineer and I've used excel macros for a very long time to automate my workflow or illustrate results, so while I don't think of myself and a coding expert (or anywhere near that), I've accumulated some knowledge over time and have never found anything I couldn't do (or work around of), more often than not with your help.

For the first time after looking everywhere, there is one issue I can't seem to have answered.

I have an excel macro that when the workbook activates, draws a shape occupying the entire screen via

.Shapes.AddShape(msoShapeRectangle, 0, 0, screenX, screenY)

, fills it with a picture via

.Fill.UserPicture (ActiveWorkbook.Path & "\Images\BGs\mainMenuBG.jpg")

and shows a userform with a menu.

The way I intended for it to work was so the picture would show up before the userform (hence after excel's spalshscreen and after itself has been drawn on screen). But what inevitably happens is that my menu (userform) shows up even before excel's splash screen.

In userforms I've found a workaround by loading the pictures onto a variable beforehand, when loading then in the initialize procedure of the form isn't enough, but for the shapes ".Fill.UserPicture" i can't get feedback like I would on a "while picVar Is Nothing".

Anyone has any ideas? I could put the picture in a form but since my macro revolves around shapes in the worksheet, I would rather keep it has it is.
Even if I couldn't change the fact that the userform would show up before, knowing when the image appears would allow me to do some kind of loading screen.

Thank you all! If not for this one, for all the questions you answered me unkowingly.

Cheers,

Tiago

 

[JLGWhiz]

If you only want to know when the picture appears, just put a message box immeidiately after your Fill.UserPicture statement that tells you the picture loaded.

 

[ptspawn]

But then i would have a msgbox interrupting my code...

 

[MrIfOnly]

I'm not sure how to test for the picture loading, but a thought on the userform loading too fast: maybe you can pause before showing it to give the picture enough time to load. Something like:

[COLOR=#333333].Shapes.AddShape(msoShapeRectangle, 0, 0, screenX, screenY)
[/COLOR][COLOR=#333333].Fill.UserPicture (ActiveWorkbook.Path & "\Images\BGs\mainMenuBG.jpg")

[/COLOR]Application.Wait Now + TimeSerial(0, 0, 5)
myUserform.Show

Regards,

CJ

 

[ptspawn]

That's I have it now, but since the loading time will be different in every computer (it's a big image), I was looking for a more elegant solution.

 

[Jaafar Tribak]

Try adding DoEvents before showing the userform and see if it works :

.Shapes.AddShape(msoShapeRectangle, 0, 0, screenX, screenY)
.Fill.UserPicture (ActiveWorkbook.Path & "\Images\BGs\mainMenuBG.jpg")
[COLOR=#ff0000][B]DoEvents[/B][/COLOR]
myUserform.Show

 

[Jaafar Tribak]

I have given a shot to this today and I think it should work if you run a loop that loops until the object in the screen area of the worksheet is the shape.

Something like this :

(You will first need to assign the values to the screenX and screenY variables in the code below )

Private Sub Workbook_Activate()

    Dim oShape As Shape
    Dim lShapeLeft As Long, lShapeTop As Long
    
    Set oShape = ActiveSheet.Shapes.AddShape(msoShapeRectangle, 0, 0, screenX, screenY)
    oShape.Fill.UserPicture (ActiveWorkbook.Path & "\Images\BGs\mainMenuBG.jpg")
    
    With ActiveWindow
        If .VisibleRange.Row <> 1 Or .VisibleRange.Column <> 1 Then
            oShape.Parent.Cells(1, 1).Activate
        End If
        lShapeLeft = .PointsToScreenPixelsX(oShape.Parent.Cells(1).Width)
        lShapeTop = .PointsToScreenPixelsY(oShape.Parent.Cells(1).Height)
        Do
            If TypeName(.RangeFromPoint(lShapeLeft, lShapeTop)) <> "Range" Then
                If .RangeFromPoint(lShapeLeft, lShapeTop).Name = oShape.Name Then
                    Exit Do
                End If
            End If
            DoEvents
        Loop
    End With
    
    myUserform.Show

End Sub

 

[ptspawn]

Wow, I can't believe i just now saw this reply. I'll have to try it asap, but it looks very good. At the very least it gives me a few new ideias.

Thank you so much. I'll tell you how it goes

 

[ptspawn]

Hey Jaafar,

Sadly, it doesn't work. I can see no change in the behavior of the macro Thank you anyway. I was super hopeful... I could see this bit of code ending on a lot more places that this one...

 

[Jaafar Tribak]

Hey Jaafar,

Sadly, it doesn't work. I can see no change in the behavior of the macro Thank you anyway. I was super hopeful... I could see this bit of code ending on a lot more places that this one...

Can you upload a sample of the workbook to a file sharing site like Box.net or somewhere else so I can replicate the problem and hopefully solve the problem
?