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
 

Some videos you may like

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
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.
 

MrIfOnly

Active Member
Joined
Nov 10, 2016
Messages
489
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:

ptspawn

New Member
Joined
Dec 29, 2017
Messages
5

ADVERTISEMENT

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

Well-known Member
Joined
Dec 5, 2002
Messages
8,118
Office Version
  1. 2016
Platform
  1. Windows
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
 

Jaafar Tribak

Well-known Member
Joined
Dec 5, 2002
Messages
8,118
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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:

ptspawn

New Member
Joined
Dec 29, 2017
Messages
5
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

New Member
Joined
Dec 29, 2017
Messages
5
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

Well-known Member
Joined
Dec 5, 2002
Messages
8,118
Office Version
  1. 2016
Platform
  1. Windows
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
?
 

Watch MrExcel Video

Forum statistics

Threads
1,123,270
Messages
5,600,637
Members
414,398
Latest member
dhune

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
Top