Set OptionBox value via VBA

[Mark Burns]

I just though I'd post the results of my experience trying to do something
which I would have thought would be simple. In the end it was simple, but you
have to know some somewhat obsure things to do it right, apparently.
The task:
An (Userforms/MSForms, NOT an ActiveX) Group control and two enclosed Option
button controls (one for Yes, one for No) are on a spreadsheet. Set the
option button value(s) to Yes or No via VBA code as read from a database
field.
How would you think this would be done?
Set the value of the ONE control which is "Picked"/On to True(=-1 in VBA),
right? Wrong. Set it to the constant value xlON (= 1 in VBA).
Also, it seems to depend upon whether the control is bound to a cell in a
workbook or not. According to my recent experience, if you do this:
ActiveWorksheet.Shapes("OptionButton1").ControlFormat.Value = xlOn
This will set an option button to ON. Interestingly, if you wish to set that
same option button to OFF you need the xlOFF constant, whose value is NOT 0
(which is what VB programmers might intuitively think it ought to be), it is
-4146. go figure.

However, this may - or may not - work properly if you are setting the value
of a control that IS bound to a worksheet cell (i.e.
ActiveWorksheet.Shapes("OptionButton1").LinkedCell="B19" ).
In that circumstance, you may be better off in changing the option button
values by changing the cell value directly.
But, what values do you use to set the option buttons in the cell? xlON and
xlOff, right? wrong.
The values you need to set in the cell are 2 or 3 in this example. 2!??
3!??? Where the HECK did those values come from? I haven't a clue. Ths only
way I found out what the values to use was to set up the control, bind them
to a common cell, and click them each, observing what values appeared in the
cell.
Well, at leasst it worked, and hopefully, this should help someone else
puzzle through this forms-control-in-worksheet weirdness too.

 

[Chip Pearson]

An (Userforms/MSForms, NOT an ActiveX) Group control

Controls from the Controls command bar are Userform/MSForms ActiveX
controls. Controls from the Forms command bar are Excel Forms
controls, originally designed for Dialog Sheets. The naming of such
things is a bit confusing. (And just to add to the confusion, ActiveX
controls are in the Windows Forms, not MSForms, family when you look
them up by PROGID in RegEdit.)

If you go into the Object Browser in VBA, you'll see the ActiveX
controls (if you have the MSForms typelib loaded), but you won't see
the Excel controls unless you turn on Show Hidden Members (right-click
in the Object Browser and choose this from the popup menu).

In code, you would reference an ActiveX option button as

Dim OptButton As MSForms.OptionButton

and you would reference an Excel option button as

Dim OptButton As Excel.OptionButton

Of you omit the typelib qualifier, e.g.,

Dim OptButton As OptionButton

the object used will be the one found in the typelib with the highest
priority, which is typically Excel, not MSForms.

The Excel.* controls are considered obsolete but are still supported.

When you put multiple Excel option buttons in a group box control,
they all have the same linked cell, regardless of what you assign to
the LinkedCell property. The LinkedCell used is the most recently
assigned LinkedCell value of any Option Button. (This may not be the
most recently created OptionButton. Create 4 option buttons in a group
box and assign the LinkCell properties to A1, A2, A3, and A4
respectively. You'll see that only A4 changes, and gets a value of 1,
2, 3, or 4, depending on which OptionButton was clicked, the value
being the order in which they were created. Now change the LinkedCell
property of OptionButton2 to A10. Now, all Option Buttons are linked
to A10. Confusing? Yeah.)

The value in that cell references the option button in the group box
that has a value of xlOn (=1). The number here is the option button in
the order in which it was created within the group box. This number is
accessible via the Index property:

Debug.Print Sheet1.OptionButtons("Opt3").Index

You can see these properties and values with code like

Sub BBB()
Dim R As Range
Dim WS As Worksheet
Dim OptB As Excel.OptionButton
Set WS = Sheet1
Set R = WS.Range("C1")
For Each OptB In WS.OptionButtons
R(1, 1) = OptB.Name
R(1, 2) = OptB.GroupBox.Name
R(1, 3) = OptB.Index
R(1, 4) = OptB.Value
R(1, 5) = OptB.LinkedCell
Set R = R(2, 1)
Next OptB
R.Resize(1, 5).EntireColumn.AutoFit
End Sub



If you want to programmatically change the value of an OptionButton,
change the linked cell to the Index of the OptionButton you want to
change:

Range("A4").Value = 2

or set the OptionButton's Value property to xlOn or xlOff. E..g,

sheet1.OptionButtons("Opt2").Value=xlOn

If you want to clear all option buttons (none selected), change the
value of the linked cell to a value < N or > N where N is the number
of option button in the group box.


Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)




On Thu, 19 Mar 2009 07:53:03 -0700, Mark Burns <Grandpa

 

[Grandpa Mark]

Chip,

Thanks for the clarifications.
The really frustrating thing is that 1) the ID #s for the controls don't
seem to be exposed anywhere in the API (or at least not in the Excel GUI),
and 2) the behavior of the controls with/without being linked to a worksheet
cell is not clearly documented anywhere that I could find.
Of course, the cute excel xlON/xlOFF constants and their nifty,
not-exactly-intuitive values doesn't exactly help avoid any confusion any
either (it wouldn't be so bad if those constants were enums for the .value
property, but apparently that was too obviously self-documenting a situation
to hope for).

Do you know if anybody's made anything like a comprehensive
document/whitepaper/website/guide to "The Proper care and feeding of Excel
and ActiveX controls in both Userforms and on Worksheets."?
I could also easily imagine the need for a similar set of guides/documents
for the multitude of SHAPE objects (_not_ counting the controls). One
specific question I have is this: where is there a document that lists all
the various valid Adjustment points for the various autoshapes? ....or is
there no such animal anywhere?