Find linked cell address

**shirleyxls** · 02-05-2011, 06:13 PM

I have a worksheet with several toggle buttons. When one of the toggle buttons is clicked it runs some code which checks the status of each toggle button (TRUE or FALSE), but I need to find the address of the linked cell to drive which columns to hide or display (I want to hide the two columns after the linked cell column), but I can't work out how to find the linked cell address.

A sample worksheet is attached (the real one is a lot more complex!). Any help would be much appreciated.

Many thanks
Shirley

**MarvinP** · 02-05-2011, 06:28 PM

Hi Shirley,

Clicking on those toggle buttons runs VBA code. If you go to the Developer Tab and look behind the sheet or in a module you will be able to see the code that is being called/run.

Look at this picture http://www.exceldigest.com/myblog/20...developer-tab/ and find the Design Mode Icon. If you select this and then click and right click the toggle buttons you can see what macro they are using and what the code behind each is doing.

hope that helps.

**shirleyxls** · 02-05-2011, 06:41 PM

Thanks for the quick reply Marvin.
Maybe I wasn't clear, but I'm developing the code to run behind the toggle buttons rather than trying to see what someone else wrote. Each button calls the same macro (I didn't want to put the same code in every button - there will be 52 of them on the final version!) so my code needs to work out which button was switched on and take some action depending on the address of the cell linked to that button. But I can't figure out the VBA code to find the linked cell related to each button.
Any ideas?
Many thanks
Shirley

**MarvinP** · 02-05-2011, 07:00 PM

Hi Shirley,

ToggleButtons are really shapes on the form. They really don't "tie to a cell" as I understand it. Perhaps before you CallExpandCollapse you should put the name of the button clicked in a cell on your sheet somewhere. Then when you got to the called sub you could read the cell to see what ToggleButton was pressed.

I'm imagining your code looking like:

Private Sub ToggleButton1_Click()
    ActiveSheet.Range("A1") = "TB1"
    Call ExpandCollapse
End Sub

Then when your get to your module code you see what is in A1 and branch based on it.

I went through the Object Browser looking for the cell a shape is attached to and found the word "ShapeRange" but couldn't make it return useful information. (I really gave me an error message which told me, once again, "you don't understand this object")

You could also declare a global variable and set it to the last button clicked and not need a cell in the workbook.

**shirleyxls** · 02-05-2011, 07:06 PM

Hey, excellent idea! Wish I'd thought of that!

I also tried the object browser before I posted my question, and also couldn't get anything relating to shapes to return any useful information, at least in this context.

I'm sure that will solve my problem... many thanks for your help.
Shirley