Show & Hide Text Boxes Doesn’t Work Correctly

[OldRookie]

I’m trying to show and hide a text box named “Start” with the following coding in a macro:
ActiveSheet.Shapes.Range(Array(”Start”)).Visible=true

ActiveSheet.Shapes.Range(Array(”Start”)).Visible=False

When these commands are encountered during execution they don’t work correctly, if at all. I’ve looked at previous Mr Excel threads and others on the Internet. As somewhat expected, there appears to be a variety of ways to do this. Some distinguish between a “drawing tool bar” and a “control toolbox toolbar”; recommending the later. Still others seemingly embellish their commands with Boolean names and separate macros, while the heart of it employs the coding I presented above. I’m confused, I’m sure I’m missing something. What are the simplest, separate, VBA commands to show and hide a text box?

Thank you,
OldRookie

 

[OldRookie]

Michael,
Stay tuned (if you would like), I’ve been trying to figure this out for 2 days, I’ll give you a better description tomorrow of how the text boxes relate to the macro. I think the bigger picture may help.

Thank you so much for your help.
OldRookie

 

[Michael M]

Ok, might be a good idea....I'll stay subscribed to this thread !!

 

[OldRookie]

Michael,

Good morning. Thank you for staying with me on this. I’ll give you a glimpse of the bigger picture.

I have a lengthy macro named “Run” which is launched by a TextBox that is now named “myGoNow”. “Run” can take a relatively long time to execute depending on input from a UserForm. Once execution of “Run” is initiated, I want to hide the “myGoNow” TextBox. In addition, because “Run” takes so long, I want to show a “Running” TextBox and hide a “Finished” TextBox. These commands are executed right after Dim statements at the very beginning of the macro before it gets into the lengthy body of it. Once execution of the body is completed, I want to hide the “Running” TextBox and show the “Finished” TextBox just before the EndSub command.

Bits and pieces of this are working, just not correctly. What’s happening is, execution of the hide “myGoNow” and show “Running” text boxes is postponed until the body of the macro is executed. They are then executed right before the hide “Running” and show “Finished” TextBoxes are executed. If you’re paying close attention all you see at the end of the macro is a brief flash of the “Running” TextBox and then finally, the “myGoNow” TextBox is hidden. The same thing happens with the various methods of show and hide we explored yesterday. From all this, I conclude there’s something about the lengthy body of this macro that’s causing it. Logically it doesn’t make any sense unless there’s parallel execution in the processor and it gets overwhelmed with the lengthy body and can’t do both that and the GUI simultaneously.

All this leads me to believe the problem is deeper than proper commands or syntax. Perhaps it’s just an idiosyncrasy I’ll have to live with. As such, it’s not the end of the world, however, I would like to understand what’s going on. Every project is an opportunity to advance my skills and understanding of VBA. Leaving a hanging question mark is what I would remember.

Thank you very much for all your patience, understanding, and help. You people at Mr Excel are great!

OldRookie

 

[Michael M]

Have you considered changing the textboxes to messages on the status bar ??
AND
IS it worth visiting the body of code to:
1. see if there is a flaw within
2. See if we can shorten / modify
3. try and speed up the code

How long is "relatively long" in the running of the macro ?

 

[OldRookie]

Michael,
I haven’t considered messages on the status bar, I’m not sure what they are but will research them.

My code is slow because I’m manipulating large arrays, cell by cell, with multiple For loops [sequential and nested (5 deep)] and IF statements. I’m absolutely positive my code could be streamlined using object properties and methods. Unfortunately I don’t have much experience with them and consequently unfamiliar with their full potential. I suspect there are hundreds of them to learn.

I don’t believe there’s an error in the logic of my archaic coding technique. I wrote it in small sections and debugged as I went. I’ve thoroughly exercised the complete code and it’s performing as expected.

My reference to taking a relatively long time to run is comparing execution time to modern computer speeds. The longest running case has an execution time of 11 seconds. This could become time consuming if I were to run it multiple times. Fortunately, it’s only an occasional occurrence, the rest of the time I run shorter/faster cases. At this point of my VBA skill modernization effort, the occasional lengthy execution time doesn’t justify the time required to learn, in-depth, the commands required to unleash the full power of VBA. With that being said though, I had planned on going back through and declaring each variable as Integer or String to speed this up. In my limited experience though, this is a fine tune-up point and I haven’t seen it make a great deal of difference. I’m willing to explore small changes to speed things up but unwilling to attack the logic.

As I mentioned at the onset, I don’t know how to employ the status bar but will research it. Another option is to put the show and hide of the Running and Finished text boxes in separate macros before and after the Run macro. That approach doesn’t address hiding the “Run” TextBox but I can live with that.

Thank you,
OldRookie

 

[Michael M]

This might help...

Show Message In Excel Status Bar - Contextures Blog

If a macro takes a long time to run, it can be frustrating to wait for it to finish. Usually the screen updating is turned off when a macro runs, so it's hard

contexturesblog.com

 

[OldRookie]

Michael M,
This is interesting, I put:
ActiveSheet.Shapes.Range(Array(“myGoNow”)).Visible=False
ActiveSheet.Shapes.Range(Array(“Running”)).Visible=True
Application.StatusBar = “ “
at the beginning of my lengthy macro and it made these show and hide commands work the way I wanted!!! I’m guessing and generalizing here, but perhaps the hierarchy/significance of the StatusBar command is high enough to cause the processor to compartmentalize the macro. Separates it into before and after the command thereby seeing my hide and show TextBoxes. All I really know is it works!

Thank you so very much for ALL of your help. You were fantastic! I’ve learned a great deal in this pursuit which definitely made it worthwhile. All roads traveled were educational.

Happy Holidays,
OldRookie

 

[Michael M]

Glad it worked for you
My only guess is by "neutralising " the Status Bar it's giving more focus to the hiding of the TextBoxes....but I still think, as mentioned previously, that the macro may be using too many resources.
Posters here are always happy to review code if you feel happy enough to share it.

 

[OldRookie]

I agree, the Status Bar is masking the real problem. I have VBA books coming and will study them with this macro in mind. It will be my study/practice aid. I suspect it will be a lengthy process, that’s why I didn’t want to get into it now. Better to get something working then go back and whittle away at it.

OldRookie