×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Show / Hide Shape VBA Excel

Show / Hide Shape VBA Excel

Show / Hide Shape VBA Excel

(OP)
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?

CODE

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

RE: Show / Hide Shape VBA Excel

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

RE: Show / Hide Shape VBA Excel

(OP)
Yes. Menu is being looked at when the macro is run.

RE: Show / Hide Shape VBA Excel

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

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

RE: Show / Hide Shape VBA Excel

(OP)
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

RE: Show / Hide Shape VBA Excel

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

CODE -->

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

RE: Show / Hide Shape VBA Excel

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

combo

RE: Show / Hide Shape VBA Excel

(OP)
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

RE: Show / Hide Shape VBA Excel

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

RE: Show / Hide Shape VBA Excel

(OP)
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.

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


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 

RE: Show / Hide Shape VBA Excel

Try two DoEvents after setting the Shape's visible property

RE: Show / Hide Shape VBA Excel

(OP)
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

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 

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login


Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close