Hi Hank ,
If you totally new to VBA , then I think the following links will help :
http://excelvbatutor.com/vba_tutorial.html
http://www.excel-vba.com/excel-vba-contents.htm
http://www.homeandlearn.org/
http://www.anthony-vba.kefra.com/vba/vbabasic1.htm
http://www.excel-pratique.com/en/vba.php
Any VBA procedure / macro has 2 sides to it ; the first and more important one is the logic or algorithm ; the step-by-step detailing of what is to be done to reach the final outcome. The second is the translation of this logic into a language which the computer understands. Going through the links given above should help you master the second step. I will detail the logic of the code.
The steps toward getting what you want are :
1. Detect which button has been clicked
2. Retrieve the picture which is linked to this button
3. Toggle the state of this picture i.e. if the picture was visible , hide it , and if it was hidden , display it.
The first step makes use of a VBA property called
Application.Caller ; in our context this tells Excel which button was clicked , but there are other facets to this function , depending on the context in which it is executed. You can see the Excel help on this for more details.
In our code , what the Application.Caller returns is a string giving the name of the button which was clicked. I have named the first button
Your button X , and so the Application.Caller property will return this , and this is being assigned to the VBA variable
ButtonClicked.
Now , we need to retrieve the picture which is linked to this button.
More than any coding , what this involves is structuring the data so that this retrieval becomes easy. What I have done is set up a matrix called LinkingArray. This has 2 columns - the first column contains the names of the buttons viz.
Your button X ,
Your button Y and
Your button A. The second column contains the names of the associated pictures. In our case , the first button is linked to a picture which I have named
Your first Picture.
If you want to customize this to your file , within the code , do a find and replace first ; search for
Your button X , and replace all instances of this with the name of the first button in your file. Do the same for all the references to buttons and pictures.
The only other thing you need to do is change a constant I have named
MAXBUTTONS ; within the code , this is set to 10 , which means you can have up to 10 buttons and 10 pictures. You need to change this only if you have more buttons / pictures than this.
The toggling of the state of the picture is by the line :
ActiveSheet.Shapes(LinkingArray(i, 2)).Visible = Not (ActiveSheet.Shapes(LinkingArray(i, 2)).Visible)
The Visible property of a shape can be either True or False ; if True , the shape is displayed ; if False , the shape is hidden.
This is what is known as a Boolean , and in this , the negation of one state is the other , which means Not True is the same as False , while Not False is the same as True.
Thus what we are telling Excel to do is take the current state of the shape and negate it , so that if it were displayed , after the execution of this statement , it would be hidden , and if it were hidden , then after the execution of the statement , it would be displayed.
I think if you go through the links given at the beginning of this post , you will understand the code in less time than it takes to go through this post !
Narayan
