Detect when Image in Shape appears

ptspawn

New Member
Joined
Dec 29, 2017
Messages
5
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
Code:
.Shapes.AddShape(msoShapeRectangle, 0, 0, screenX, screenY)
, fills it with a picture via
Code:
.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
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
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.
 
Upvote 0
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:

Code:
[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
 
Last edited:
Upvote 0
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.
 
Upvote 0
Try adding DoEvents before showing the userform and see if it works :
Code:
.Shapes.AddShape(msoShapeRectangle, 0, 0, screenX, screenY)
.Fill.UserPicture (ActiveWorkbook.Path & "\Images\BGs\mainMenuBG.jpg")
[COLOR=#ff0000][B]DoEvents[/B][/COLOR]
myUserform.Show
 
Upvote 0
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 )

Code:
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
 
Last edited:
Upvote 0
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
 
Upvote 0
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...
 
Upvote 0
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
?
 
Upvote 0

Forum statistics

Threads
1,213,530
Messages
6,114,162
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top