Display/Hide image while code is working

[Vintage79]

Hello all,

I would like to display a 'please wait' image while my code is working. So, Sheet1 is for the user to press a button and activate the code. On the hidden Sheet2 I have an image that I have named as 'Image01'.

I'm thinking that I could use something like this:

When button is pressed to run code:

Call Image01 from Sheet2 and display it on Sheet1
Application.ScreenUpdating = False
Code then runs here
Application.ScreenUpdating = True
Hide Image01

Is this possible? Thanks for any advice!

 

[Jeffrey Mahoney]

So here is something I learned today. To load a picture from a sheet into a userform, it first has to be saved as a file first. Before it can be saved to a file, it has to be added to a chart. Sheesh

In the code below, the variable PathName is loaded from a named range called "PathName" I added to SETUP sheet. I put "C:\Temp\Pict.jpg" in the cell. You may need to reference a different folder. Change the names of the sheets and picture names as you need.


This is the code in the UserForm that gets called when the form is loaded. I named it "PleaseWait"
It copies the picture I named "PleaseWaitPict" in a new temporary chart, then exports it to a file. It then deletes the chart. The file gets loaded in the form into a Image Control still called Image1. The file gets deleted also. At the end the OnTime function calls the ClosePicture SUB after 5 seconds.

Private Sub UserForm_Initialize()
  Dim Sht As Worksheet
  Dim Pic As Object
  Dim PathName As String
  Dim Cht As ChartObject
  
  PathName = Sheets("SETUP").Range("PathName").Value
  Set Sht = Sheets("Sheet1")
  
  For Each Pic In Sht.Pictures
    If Pic.Name = "PleaseWaitPict" Then
      Set Cht = Sht.ChartObjects.Add(100, 0, Pic.Width, Pic.Height)
      Cht.ShapeRange.Fill.Visible = msoFalse
      Cht.ShapeRange.Line.Visible = msoFalse
      Sht.Shapes(Pic.Name).Copy
      Cht.Activate
      ActiveChart.Paste
      Cht.Chart.Export PathName
      Cht.Delete
      Image1.Picture = LoadPicture(PathName)
      Kill PathName
      Exit For
    End If
  Next Pic
  
  
  Application.OnTime EarliestTime:=Now() + TimeSerial(0, 0, 5), Procedure:="ClosePicture", LatestTime:=Now() + TimeSerial(0, 0, 30), Schedule:=True
  
End Sub

This code must be put into a standard module

Sub ClosePicture()
  Unload PleaseWait
End Sub

 

[Vintage79]

Thank you for this suggestion. Unfortuantely this solution wouldn't work for me, as it would require any user of the code, to first copy the image to a specific location on their machine. I was hoping there might be a way to do this by already having the image in the workbook.

 

[Tetra201]

Why not to have Image01 hidden on Sheet1 and make it visible only while the code is running?

Sheets("Sheet1").Shapes("Image01").Visible = True
Application.ScreenUpdating = True
Application.ScreenUpdating = False
...
Sheets("Sheet1").Shapes("Image01").Visible = False
Application.ScreenUpdating = True

 

[Vintage79]

Actually, this could work! But for some reason, the following works perfectly when I step through, but not when I run the code in the normal way:

Sheets("Sheet1").Shapes("Image01").Visible = True
Application.Wait (Now + TimeValue("0:00:05"))
Sheets("Sheet1").Shapes("Image01").Visible = False

When I run that, it flashes the image up for the tiniest fraction of a second, just at the point of line 3.

Any idea why this could be?

I tried this too, but the resutls were the same:

Sheets("Sheet1").Shapes("Image01").Visible = True
Application.ScreenUpdating = False
Application.Wait (Now + TimeValue("0:00:05"))
Application.ScreenUpdating = True
Sheets("Sheet1").Shapes("Image01").Visible = False

 

[Tetra201]

Take a look at the 2nd line of code in Post #4

 

[Vintage79]

Take a look at the 2nd line of code in Post #4

So, I thought that was a mistake and left it out!

Sheets("Sheet1").Shapes("Image01").Visible = True
Application.ScreenUpdating = True
Application.ScreenUpdating = False
Application.Wait (Now + TimeValue("0:00:03"))
Sheets("Sheet1").Shapes("Image01").Visible = False
Application.ScreenUpdating = True

Ok, so this is what I have now, but still it only works on step-through.

 

[Vintage79]

Tried this too, but no joy:

Sheets("Sheet1").Shapes("Image01").Visible = True
Application.ScreenUpdating = True
ActiveWorkbook.RefreshAll
Application.ScreenUpdating = False
Application.Wait (Now + TimeValue("0:00:03"))
Sheets("Sheet1").Shapes("Image01").Visible = False
Application.ScreenUpdating = True

 

[LazyBug]

So, I thought that was a mistake and left it out!

Sheets("Sheet1").Shapes("Image01").Visible = True
Application.ScreenUpdating = True
Application.ScreenUpdating = False
Application.Wait (Now + TimeValue("0:00:03"))
Sheets("Sheet1").Shapes("Image01").Visible = False
Application.ScreenUpdating = True

Ok, so this is what I have now, but still it only works on step-through.

If the picture is only needed for the time of code execution, you can make separate macros to show and hide it, and insert their call at the beginning and end of the code, respectively. Another option is to just show Sheet2 while the code is running, and hide it as soon as it finishes.

 

[Vintage79]

If the picture is only needed for the time of code execution, you can make separate macros to show and hide it, and insert their call at the beginning and end of the code, respectively. Another option is to just show Sheet2 while the code is running, and hide it as soon as it finishes.

I tried the first suggestion, but that had the same results:

Sub ImageShow()
Sheets("Sheet1").Shapes("Image01").Visible = True
Application.ScreenUpdating = True
End Sub

Sub ImageHide()
Sheets("Sheet1").Shapes("Image01").Visible = False
End Sub

Sub wait()
Call ImageShow
Application.ScreenUpdating = False
Application.wait (Now + TimeValue("0:00:03"))
Application.ScreenUpdating = True
Call ImageHide
End Sub

I'd rather do it this way than displaying a different sheet, and it seems tantalisingly close, as it works perfectly on step-through.