Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find the cell that is the option button is on it
Hello,
For example I have 400 option buttons, so i want to find on which cell they are. I mean, if option button is on cell (2,2) I want to know this. Is it possible? Regards, |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find the cell that is the option button is on it
Hi Supper Duck,
If the optionButtons are from the Forma controls, try somethoing like: '============= Public Sub Tester() Dim oButton As OptionButton For Each oButton In ActiveSheet.OptionButtons oButton.OnAction = "TestIt" Next oButton End Sub '------------------- Public Sub TestIt() MsgBox ActiveSheet.OptionButtons _ (Application.Caller).TopLeftCell. _ Address(External:=True) End Sub '<<============= --- Regards, Norman "SupperDuck" wrote in message ... Hello, For example I have 400 option buttons, so i want to find on which cell they are. I mean, if option button is on cell (2,2) I want to know this. Is it possible? Regards, |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find the cell that is the option button is on it
I don't think that is possible, because the option buttons are shapes and
don't really have locations in cells, although I maybe wrong. My best guess is that you would create a helper column or worksheet that you link the option buttons to, and next to them you can also put their location, etc. "SupperDuck" wrote: Hello, For example I have 400 option buttons, so i want to find on which cell they are. I mean, if option button is on cell (2,2) I want to know this. Is it possible? Regards, |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find the cell that is the option button is on it
Which type of option button - control toolbox toolbar or forms toolbar?
for forms toolbar, in the click event set rng = Application.Caller If you are looping through option buttons, show the code and where you want to know the cell below. Or look at the topLeftCell property of the OleObject for control Toolbox controls or for OptionButton object for forms controls -- regards, Tom Ogilvy "SupperDuck" wrote: Hello, For example I have 400 option buttons, so i want to find on which cell they are. I mean, if option button is on cell (2,2) I want to know this. Is it possible? Regards, |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find the cell that is the option button is on it
Maybe you can look at the .topleftcell property of the optionbutton.
If you have trouble implementing this, you may want to give more info. Are these optionbuttons from the Forms toolbar or optionbuttons from the control toolbox toolbar? When do you want to determine where each is located? When you click on the optionbutton or some other time? SupperDuck wrote: Hello, For example I have 400 option buttons, so i want to find on which cell they are. I mean, if option button is on cell (2,2) I want to know this. Is it possible? Regards, -- Dave Peterson |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find the cell that is the option button is on it
mental glitch
or forms toolbar, in the click event set rng = Application.Caller should be or forms toolbar, in the click event Dim btn as OptionButton, rng as Range set btn = activesheet.OptionButtons(application.Caller) set rng = btn.topleftcell -- Regards, Tom Ogilvy "Tom Ogilvy" wrote: Which type of option button - control toolbox toolbar or forms toolbar? for forms toolbar, in the click event set rng = Application.Caller If you are looping through option buttons, show the code and where you want to know the cell below. Or look at the topLeftCell property of the OleObject for control Toolbox controls or for OptionButton object for forms controls -- regards, Tom Ogilvy "SupperDuck" wrote: Hello, For example I have 400 option buttons, so i want to find on which cell they are. I mean, if option button is on cell (2,2) I want to know this. Is it possible? Regards, |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find the cell that is the option button is on it
Hello,
It is control toolbox Regards, "Tom Ogilvy" wrote: Which type of option button - control toolbox toolbar or forms toolbar? for forms toolbar, in the click event set rng = Application.Caller If you are looping through option buttons, show the code and where you want to know the cell below. Or look at the topLeftCell property of the OleObject for control Toolbox controls or for OptionButton object for forms controls -- regards, Tom Ogilvy "SupperDuck" wrote: Hello, For example I have 400 option buttons, so i want to find on which cell they are. I mean, if option button is on cell (2,2) I want to know this. Is it possible? Regards, |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find the cell that is the option button is on it
If looping through all the optionbuttons:
for each obj in Activesheet.OleObjects if typeof obj.Object is MSForms.OptionButton then msgbox obj.Name & ": " & obj.TopLeftCell.Address end if Next In any event, each option button is contained by an OleObject that has a topLeftCell property and a bottomRightCell property. If you have a specific optionbutton and you are using it specifically in your code. msgbox "OptionButton1: " & ActiveSheet.OptionButton1.TopLeftCell demo'd from the immediate window: ? Activesheet.optionbutton1.TopLeftCell.address $I$11 ? activesheet.OleObjects("OptionButton1").TopLeftCel l.Address $I$11 Note that in Excel 97, the OleObject Name and the Control Name may not be the same. -- Regards, Tom Ogilvy "SupperDuck" wrote: Hello, It is control toolbox Regards, "Tom Ogilvy" wrote: Which type of option button - control toolbox toolbar or forms toolbar? for forms toolbar, in the click event set rng = Application.Caller If you are looping through option buttons, show the code and where you want to know the cell below. Or look at the topLeftCell property of the OleObject for control Toolbox controls or for OptionButton object for forms controls -- regards, Tom Ogilvy "SupperDuck" wrote: Hello, For example I have 400 option buttons, so i want to find on which cell they are. I mean, if option button is on cell (2,2) I want to know this. Is it possible? Regards, |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find the cell that is the option button is on it
Plan ahead: name the controls to include their location.
Eg. opt_B2 Then you can get the control's (intended) location directly from its name using dim rng as range set rng=shtObject.range(split(controlname,"_")(1)) Tim "SupperDuck" wrote in message ... Hello, For example I have 400 option buttons, so i want to find on which cell they are. I mean, if option button is on cell (2,2) I want to know this. Is it possible? Regards, |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Option Button - Cell Link Issue | Excel Programming | |||
Help to get values from 3 option button in one cell | Excel Programming | |||
Find and read/update option button | Excel Programming | |||
Control Cell Link for Option Button based on value in a cell | Excel Programming | |||
Control Cell Link for Option Button based on value in a cell | Excel Programming |