Find the cell that is the option button is on it

[Guest]

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,

 

[Norman Jones]

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
'<<=============

 

[Guest]

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.

 

[Guest]

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

 

[Dave Peterson]

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?

 

[Guest]

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

 

[Guest]

Hello,

It is control toolbox

Regards,

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

 

[Guest]

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").TopLeftCell.Address
$I$11

Note that in Excel 97, the OleObject Name and the Control Name may not be
the same.

 

[Tim]

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