This quick tip shows you how to show or hide an object in Excel with a button click.
So, you guessed it! Once you learn how to do this, just think about all of the possibilities. For example, design your own game or a dashboard in Excel, like Jeopardy or a financial tool.
Of course, you will need to learn other things besides showing and hiding an object. However, it is a great and fun start.
If you prefer to view the video version, then click here.
Before we go through all the steps in a visual manner, let's look at the steps in a quick glance.
Essentially, that is what we plan to do.
For the purpose of this lesson, we suggest you use Excel 2007 or higher or compatible.
So, our object and buttons are ready to go. If you do not have your objects ready, then create some or just follow along.
After you do this, your object now has a custom name. Ours is shpShip. Overall, we prefer using camel case when creating names and variables.
Also, notice that we prefix our objects. For example, we prefix a shape or graphic with shp. And, buttons with btn.
Most of the time, we try to keep the prefix at three characters. In addition, it helps use distinguish between reserved words.
Next, use the same process to name your buttons. See images below.
Notably, the names of our buttons are btnShow and btnHide.
We really do not need to do anything at this moment. Therefore, just stop recording as shown below.
Next, follow the same process to access a macro to the other button. Instead, give the macro the name hide_object. See the image below.
If you are having trouble, then here are related quick tips from our library that you can use to refresh your knowledge of macros.
Otherwise, skip this and continue with the lesson.
Finally, it is time to go into the code and do some things add functionality to our buttons. Follow these steps to update the macro code.
The Visual Basic for Applications (or VBA) code window opens. Similarly, your view should look like the image below.
So, follow these steps to change the code for the show and hide buttons.
If you have a single quotation mark at the start of your line, then delete it. It represents a comment and will not work. Also, make sure you have the true and false codes in the correct routines.
Since VBA opens in its own window, you have to return back to Excel.
Now that you have your code set, we must assign it to the buttons and test it out. Follow the steps below to assign macros to your buttons.
Follow the same method for the other button.
After you finish assigning the macros, click somewhere on the sheet to unselect the buttons. Then, you can test out the buttons.
If you choose to save your work, then you should save it as a macro-enabled file. This way, your code will remain intact with your Excel file.
Below is a quick video of this lesson.
So, did it work for you? When you clicked on the hide button, did it hide your object? And, vice versa?
If not, then give it another shot.
How to show or hide an object in Excel with a button click
by Excel Effects :: November 3, 2020