Show / Hide Shape VBA Excel 1

[remeng]

Hi Folks,

Got a new one for you all. I am trying to show and hide a shape when a macro is running. The shape is called "error_check_status". The following code works when I step through it, but when I actually run the code, the shape doesn't appear at all.

I've tried removing the ScreenUpdate code as well as adding a WAIT for 5 seconds before continuing, but neither of those methods are working to allow for the shape to have time to "load".

Any ideas as to why the code works when it is stepped through, but not when it is normally run?

Sub error_check()

Application.ScreenUpdating = True 'Turns on visible Updates while the macro is running

ActiveWorkbook.Sheets("menu").Shapes("error_check_status").Visible = False 'Turns off "ERROR CHECK IN PROGRESS..." Shape
ActiveWorkbook.Sheets("menu").Shapes("error_check_status").Visible = True 'Turns on "ERROR CHECK IN PROGRESS..." Shape

Application.ScreenUpdating = False 'Turns Off visible Updates while the macro is running

'More Code

Thanks,

Mike

 

[Andrzejek]

Are you looking at the worksheet "menu" when you run this code?
Of at some other sheet in your Workbook?

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[remeng]

Yes. Menu is being looked at when the macro is run.

 

[SkipVought]

If you step through error_check, does the shape become visible?

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[remeng]

Hi Skip,

Yes it does. It also hides when it is supposed to. It just isn't visible until the code is either paused or stops mid macro.

Very odd...

Mike

 

[combo]

If you accept ScreenUpdating=True when your code is running:

Sub error_check()

Application.ScreenUpdating = True 'Turns on visible Updates while the macro is running

ActiveWorkbook.Sheets("menu").Shapes("error_check_status").Visible = False 'Turns off "ERROR CHECK IN PROGRESS..." Shape
DoEvents
ActiveWorkbook.Sheets("menu").Shapes("error_check_status").Visible = True 'Turns on "ERROR CHECK IN PROGRESS..." Shape
DoEvents

'More Code

combo

 

[combo]

For perceptive users you can use status bar instead of shape:
[tt]Application.StatusBar = "ERROR CHECK IN PROGRESS..." ' when processing[/tt]
and
[tt]Application.StatusBar = False ' restore excel messages[/tt]

combo

 

[remeng]

Hi Combo,

That is also a good idea and I will definitely incorporate that. Is there another way to make a BIG freaking message appear on the screen like a shape and then disappear when the macro is done running?

Mike

 

[SkipVought]

There is a technique that I've used to "force" users to enable macros. It involves making one sheet a splash message that displays "Enable Macros", while ALL other sheets are Very Hidden until macros are enabled and the Open macro runs, displaying all necessary sheets.

You could just make a sheet Visible and Active that just has your message while your macro runs, but your method should work.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[remeng]

Skip,

I tried combo's status bar solution to see if there is process time for the shape to appear and be recognized and then disappear. The macro with the test small file takes about five to 10 seconds. Definitely enough time to see the shape appear and then hide.

I also added a way to change the cursor icon so it appears during the macro run time.

I still want to get the shape to work somehow though since it is a lot cleaner for the user to see.

Sub error_check_status_running() 'indicates to the user that the error check macro is running

Application.StatusBar = False
Application.StatusBar = "ERROR CHECK IN PROGRESS..."
Application.Cursor = xlWait     'Sets the cursor to the wait (hour glass icon)
ActiveWorkbook.Sheets("menu").Shapes("error_check_status").Visible = True 'Turns on "ERROR CHECK IN PROGRESS..." Shape


End Sub

Sub error_check_status_end() 'indicates to the user that the error check macro is finished

Application.StatusBar = False
Application.Cursor = xlDefault 'Sets the cursor to the default icon
ActiveWorkbook.Sheets("menu").Shapes("error_check_status").Visible = False 'Turns off "ERROR CHECK IN PROGRESS..." Shape

End Sub

 

[strongm]

Try two DoEvents after setting the Shape's visible property

 

[remeng]

Thanks Strongm!

WOOOHOOOO!!! That worked perfectly. I did notice that if the sheet has not been clicked on it just before the macro is run, it sometimes will only flash the shape quickly and disappear. I'm ok with that.

final code

Sub error_check_status_running() 'indicates to the user that the error check macro is running

Application.StatusBar = False
Application.StatusBar = "ERROR CHECK IN PROGRESS..."
Application.Cursor = xlWait     'Sets the cursor to the wait (hour glass icon)

ActiveWorkbook.Sheets("menu").Shapes("error_check_status").Visible = True 'Turns on "ERROR CHECK IN PROGRESS..." Shape
DoEvents
DoEvents



End Sub

Sub error_check_status_end() 'indicates to the user that the error check macro is finished

Application.StatusBar = False
Application.Cursor = xlDefault 'Sets the cursor to the default icon
ActiveWorkbook.Sheets("menu").Shapes("error_check_status").Visible = False 'Turns off "ERROR CHECK IN PROGRESS..." Shape
DoEvents
DoEvents


End Sub