Grouping radio buttons?

[Fabz]

I am writing a ComAddIn using VSTO (Excel 2007, Visual Studio/VSTO 2008).
Now I want to add some radio buttons
(Microsoft.Office.Tools.Excel.Controls.RadioButton) on my sheet. However, I
just don't get the idea and it's hard to find any useful information. For
any reason the radio buttons don't seem to be connected, this means when
selecting one of them, the others will not be unselected.

1. Why is this so? And thus, how do I group the radio buttons? Do I
explicitly have to use GroupBox or Panel or something alike? Or is there
some kind of GroupName-Property I can use (I cannot find one)? I also tried
using Controls.AddRadioButton(myRange, myRadioButtonName) dynamically, but
this lead to the same problem.

2. Assuming I indeed have to use some kind of GroupBox, the radio buttons
seem to be positioned according to the GroupBox itself. But I do not want
GroupBox to define their position but the cells into which I originally
positioned the radio buttons. Is there a way to do this?

Thanks for your help.

 

[FSt1]

hi
radio button are sometimes call options buttons and yes, they do have a
group property which you have to go to and type in the name of the group it
belongs to.
see this site for a short tutorial.

http://www.functionx.com/excel/articles/radiobuttons.htm

regards
FSt1

 

[arjen van der wal]

Hi Fabz,

There is a GroupName property to group option buttons together. I'm not
that familiar with VSTO, but when assigning the property by code in VBA this
works:

Sheet1.OptionButton1.GroupName = "GroupA1"

Hopefully his helps a bit.

 

[John Keith]

see this site for a short tutorial.

http://www.functionx.com/excel/articles/radiobuttons.htm

Thank you for posting this url (although the images did not load for
me :-( )

I have been looking for a tutorial on radio buttons and hadn't found
anything good yet and I want to learn more about them now that I have
a situation where I can use them. If anyone has any other good sites
on radio buttons please post.


John Keith
(e-mail address removed)

 

[Fabz]

Sorry, forgot to mention that I am writing C# code for my ComAddIn. Although
there indeed is an OptionButton interface in my
Microsoft.Office.Interop.Excel namespace, it does not seem to be the proper
way to implement radio buttons (this might be different using VBA code).
What is confusing to me is that there is no GroupBox item inside my VSTO
toolbox to be chosen and drag'n'dropped on my worksheet. I only can do this
programmatically. Don't know why.

However, finally I got a working example:

// 1. Make new GroupBox instance
GroupBox myGroupBox = new GroupBox();

// 2. Add radio buttons
myGroupBox.Controls.Add(radioButton1);
myGroupBox.Controls.Add(radioButton2); // etc.

// 3. Position radio buttons in relation to the GroupBox
myGroupBox.Controls[1].Left = targetDataRadioButtons.Controls[0].Right + 10;

// Position the group box inside cells [row: 3, col: 2] and [row: 3, col: 8]
Excel.Range left = (Excel.Range)Cells[3, 2];
Excel.Range right = (Excel.Range)Cells[3, 8];

// 4. IMPORTANT: Add the GroupBox to this sheet's collection of Controls!
Controls.AddControl(myGroupBox, Range[left, right], "foo");


What is weird also is the 3rd point. To position the radio buttons, I have
to do as stated. When trying to position the radio buttons themselves, this
does not work:
// Works:
myGroupBox.Controls[1].Left = targetDataRadioButtons.Controls[0].Right + 20;

// Does not work:
radioButton2.Left = radioButton1.Right + 20;

Don't know whether there is a good solution to this problem.

Greetz
Fabz

 

[Fabz]

Sorry, the correct example must be:

// 1. Make new GroupBox instance
GroupBox myGroupBox = new GroupBox();

// 2. Add radio buttons
myGroupBox.Controls.Add(radioButton1);
myGroupBox.Controls.Add(radioButton2); // etc.

// 3. Position radio buttons in relation to the GroupBox
myGroupBox.Controls[1].Left = myGroupBox.Controls[0].Right + 10;

// Position the group box inside cells [row: 3, col: 2] and [row: 3, col: 8]
Excel.Range left = (Excel.Range)Cells[3, 2];
Excel.Range right = (Excel.Range)Cells[3, 8];

// 4. IMPORTANT: Add the GroupBox to this sheet's collection of Controls!
Controls.AddControl(myGroupBox, Range[left, right], "foo");


And thus also:
// Works:
myGroupBox.Controls[1].Left = myGroupBox.Controls[0].Right + 20;

// Does not work:
radioButton2.Left = radioButton1.Right + 20;

Greetz
Fabz

Sorry, forgot to mention that I am writing C# code for my ComAddIn.
Although there indeed is an OptionButton interface in my
Microsoft.Office.Interop.Excel namespace, it does not seem to be the
proper way to implement radio buttons (this might be different using VBA
code). What is confusing to me is that there is no GroupBox item inside my
VSTO toolbox to be chosen and drag'n'dropped on my worksheet. I only can
do this programmatically. Don't know why.

However, finally I got a working example:

// 1. Make new GroupBox instance
GroupBox myGroupBox = new GroupBox();

// 2. Add radio buttons
myGroupBox.Controls.Add(radioButton1);
myGroupBox.Controls.Add(radioButton2); // etc.

// 3. Position radio buttons in relation to the GroupBox
myGroupBox.Controls[1].Left = targetDataRadioButtons.Controls[0].Right +
10;

// Position the group box inside cells [row: 3, col: 2] and [row: 3, col:
8]
Excel.Range left = (Excel.Range)Cells[3, 2];
Excel.Range right = (Excel.Range)Cells[3, 8];

// 4. IMPORTANT: Add the GroupBox to this sheet's collection of Controls!
Controls.AddControl(myGroupBox, Range[left, right], "foo");


What is weird also is the 3rd point. To position the radio buttons, I have
to do as stated. When trying to position the radio buttons themselves,
this does not work:
// Works:
myGroupBox.Controls[1].Left = targetDataRadioButtons.Controls[0].Right +
20;

// Does not work:
radioButton2.Left = radioButton1.Right + 20;

Don't know whether there is a good solution to this problem.

Greetz
Fabz

Thank you for posting this url (although the images did not load for
me :-( )

I have been looking for a tutorial on radio buttons and hadn't found
anything good yet and I want to learn more about them now that I have
a situation where I can use them. If anyone has any other good sites
on radio buttons please post.


John Keith
(e-mail address removed)

 

[Peter T]

I can't answer your particular VSTO / RadioButton question, however from a
quick look here the control does not appear to have the equivalent of
"Group" property
http://msdn.microsoft.com/en-us/lib...l.controls.radiobutton_properties(VS.80).aspx

Maybe it's up to the programmer to turn all other RadioButtons off when one
is turned on (probably some other way though).

Perhaps you could use Excel's built in ActiveX OptionButton for your needs
(can add manually or programmatically). These have a GroupName property,
which by default is the sheet name at the time the control was added. Your
ComAddin should be able to trap all the control's events in the same way it
might trap any other Excel events.

Regards,
Peter T

 

[20pictures]

Basic Tutorial

I would like to iterate the request made by John Keith for some basic tutorial on setting up Options. I have searched for hours on the internet, been to the library, read:
Visual Basic - Abe Holmes
Brilliant VBA for Office 2007 - Paul McFedries
but still no answers.

Once past the initial step of choosing a frame or GroupName to link the options all the explanations cease or leap into complex programming!

I am creating a form in Excel 2007, with a 'radio button' option:
(Name): optMale
Caption: Male
GroupName: Gender
Value: False

(Name): optFemale
Caption: Female
GroupName: Gender
Value: False

However, I have not found any simple explanation as to how to assign events.
I tried inserting into code:

Private Sub LoadRow()
GroupNameGender.Value = Cells(lCurrentRow, 17).Value
End Sub

Private Sub SaveRow()
GroupNameGender.Value = Cells(lCurrentRow, 17).Value
End Sub

but this is clearly wrong. I need to use the form for both reading & writing to the spreadsheet.

John

 

[20pictures]

http://www.fontstuff.com/downloads/index.htm

...some useful downloads here, especially the VBA UserForms.pdf
...good guide to all Form Input controls

...at last!