Excel Effects

Reference guide.

How to show or hide an object in Excel with a button click – Quick tip

How to show or hide an object in Excel with a button click

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.


Steps in a glance

Before we go through all the steps in a visual manner, let's look at the steps in a quick glance.

  1. First, assign names to all your objects. This will allow you to easily assign and access properties for each object.
  2. Second, record a temporary macro for your button. Now, once you get good, you could skip this step and go directly into the code.
  3. Third, change the macro code to show or hide an object after the button is clicked.
  4. Lastly, assign the macros to the show and hide buttons.

Essentially, that is what we plan to do.


For the purpose of this lesson, we suggest you use Excel 2007 or higher or compatible.


Steps on how to show or hide an object in Excel with a button click

Object to show or hide with buttons.

So, our object and buttons are ready to go. If you do not have your objects ready, then create some or just follow along.

First, name your objects

Naming an object in Excel

  1. Click on your object.
  2. In the Name Box, enter a name for your object and press Enter.

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.

Naming our button as btnShow in Excel

Naming our button as btnHide in Excel


Notably, the names of our buttons are btnShow and btnHide.


Second, create macros or code for our show and hide buttons

Steps to record a macro in Excel

  1. Click on the Show button. Note: This is an optional step that helps you remember to name the macro show or hide.
  2. From the main menu bar, click Developer.
  3. Under the Developer ribbon, click Record Macro from the Code group. The Record Macro dialog box opens.
  4. Under Macro name, give your macro a name. To follow along, we suggest you enter show_object.
  5. Click OK. This starts the recording process.

We really do not need to do anything at this moment. Therefore, just stop recording as shown below.


Stop recording the macro

  1. Under the Developer ribbon, click Stop Recording.

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.

Assign name to macro for hide button


Quick tips on macros in Excel

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.


Third, change the macro code to show or hide an object after the button is clicked

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.

Show macro code in the Visual Basic for Applications editor

  1. From the menu, click Developer.
  2. Under the Developer ribbon, click Macros from the Code group. The Macro dialog box opens.
  3. In the Macro dialog box, select your macro.
  4. And, click Edit.

The Visual Basic for Applications (or VBA) code window opens. Similarly, your view should look like the image below.


Visual Basic for Applications editor

So, follow these steps to change the code for the show and hide buttons.

VBA code to show or hide the object

  1. In the hide_object() subroutine, type:
    ActiveSheet.Shapes("shpShip").Visible = False
  2. And, do the same in the show_object() subroutine, as:
    ActiveSheet.Shapes("shpShip").Visible = True
  3. Return back to Excel.

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.


Lastly, assign the macro code to the show and hide buttons

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.

Shortcut menu to assign macro to button

  1. Right-click on a button.
  2. Select Assign Macro... from the shortcut menu. The Assign Macro dialog box opens.

Assign macro dialog box

  1. Select the macro you want to assign. Since we are working with the Show button, we selected show_object.
  2. Click OK.

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.


Saving your work

Saving your Excel file as a macro-enabled workbook

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.


The video

Below is a quick video of this lesson.

https://youtu.be/G1xM3B9q8ho

Success!

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


Online courses  Home

Techronology

Artplotty Grid

Development Category (English)300x250

Microsoft Surface Book 3