Time delayed self-disappearing Shape

kabanero

New Member
Joined
Dec 2, 2018
Messages
8
Hello, VBA Nation. I am using Excel for Mac 2016. I would like a Shape to popup and disappear in a few seconds after a macro is finished. It works in Step Into mode, but does not when is attached to a button or ran from VBA Editor.The Shape does not want to appear. I will appreciate any input.

Sub PopupAndGo()
' Popup is a Shape with a text
With ActiveSheet
.Shapes("Popup").Visible = msoCTrue
Application.Wait Now + TimeSerial(0, 0, 5)
.Shapes("Popup").Visible = msoFalse
End With
End Sub
 

Some videos you may like

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,838
Hi
Welcome to the board

Try:

Code:
Sub PopupAndGo()
' Popup is a Shape with a text
With ActiveSheet
    .Shapes("Popup").Visible = msoCTrue
    Application.ScreenUpdating = True ' refreshes the screen
    Application.Wait Now + TimeSerial(0, 0, 5)
    .Shapes("Popup").Visible = msoFalse
End With
End Sub
 
Last edited:

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,838
The solution I posted was just to make yours work.

I would not do it like that. While the Wait is executing you can do nothing else.
I would instead lauch a timer. This way, during the 5 seconds I could be doing something else in excel.

For ex.:

Code:
Sub PopupAndGo()
' Popup is a Shape with a text
Worksheets("Sheet1").Shapes("Popup").Visible = msoCTrue
Application.OnTime Now + TimeSerial(0, 0, 5), "ShapeVisible"
End Sub

Sub ShapeVisible()
Worksheets("Sheet1").Shapes("Popup").Visible = msoFalse
End Sub
 

kabanero

New Member
Joined
Dec 2, 2018
Messages
8
This works!
Thanks a lot. I had been itching about this little code.
One more question if I could:
I did not disable ScreenUpdating. Why Excel did not do it on its own?
Google gives only problems re: this, but it seems its the way Excel works.
When I should insert ScreenUpdating in case I did not disable it?
Thank you very much again.
 

kabanero

New Member
Joined
Dec 2, 2018
Messages
8

ADVERTISEMENT

This is neat.
Thank you for showing this from a different angle.
 

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,838
Like I said in my second post, you cannot do anything in excel while the Wait executes.
Excel does not even run the internal refresh screen code.
When the Wait ends you immediately set the visible to false, so you never see the shape visible.
The Application.ScreenUpdating = True, in this case, is just to force the refresh on the screen before starting the Wait.

Like I said in my second post, this is not a good solution

The solution in my second post is better. You launch a timer and you can continue to work on excel during those 5 seconds.
 

kabanero

New Member
Joined
Dec 2, 2018
Messages
8
Your solution is much better.
I already have more applications for it.
Thank you.
 

Watch MrExcel Video

Forum statistics

Threads
1,109,040
Messages
5,526,412
Members
409,701
Latest member
nitmani

This Week's Hot Topics

Top