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,

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,

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,

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,

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

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,

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,

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,

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,