radiobuttonx value in excel sheet

[eHanSolo]

hi there,

i have about 3 radio buttons embedded in a work sheet in excel.

is it possible to find out which radiobutton is currently active?

and is it possible to change its value?

I know how to do this if the radio buttons were in a form but i have no idea how it works when embedded in a work sheet!

any help is appreciated!

e

 

[SkipVought]

Hi,

You can find the state.

Assuming that each option button NAME begins with "obn", then

dim shp as shape
for each shp in mysheet.shapes
  with shp
    select case .name
       case "obn"  ' option buttons
         MsgBox "Option Button " & .name & " state is " & .OLEFormat.Object.Value
       case "cbn"  ' command buttons

       case "cbx"  ' combo boxes
    end select
  end with
next

Skip,

_{When a wee mystic is on the loose..
It's a Small Medium at Large!}

 

[rmikesmith]

e,

I assume by "active" you mean the one selected. Here is a function that will return the name of the selected OptionButton on a worksheet. You must use the OptionButton control from the Control Toolbox toolbar rather than the Forms toolbar. The GroupName parameter corresponds to the OptionButton's GroupName property, which you can set from the Properties shortcut menu. This allows you to have more than one mutually exclusive group of OptionButtons on a worksheet.

Function FindActiveOptionButton(wksSource as Worksheet, ByVal GroupName As String) As String
Dim oOB As OLEobject

   With wksSource
     For Each oOB In .OLEObjects
       If TypeName(oOB.Object) = "OptionButton" Then
         If oOB.Object.GroupName = GroupName Then
           If oOB.Object.Value Then
             FindActiveOptionButton = oOB.Name
             Exit For
           End If
         End If
       End If
     Next oOB
   End With
   
End Function

Hope this helps

Regards,
Mike

 

[eHanSolo]

thanks for that skip. I'm trying to activate a button (have that black dot on it) regardless of what other button is currently selected.

I can call the macro which i've assigned to the button but i can't get that black dot (as if someone physically clicked on it) to appear.

Does this make it clearer?

Call Optionbutton1_Click

 

[rmikesmith]

e,

In your macro, try something like

Worksheets("Sheet1").OLEObjects("OptionButton1").Object.Value = True

Regards,
Mike