OptionButton Help

[Guest]

I'm trying to change the OptionButton group names to the name of the 'Current Sheet' name automatically. Is there protocol to assigning a name without having to change it every time I copy the worksheet?

 

[Dave Peterson]

You could use a macro--especially if you had lots:

Option Explicit
Sub testme()

Dim OLEObj As OLEObject
Dim newWks As Worksheet
Dim curWks As Worksheet

Set curWks = Worksheets("sheet1")

curWks.Copy _
after:=curWks

Set newWks = ActiveSheet

With newWks
For Each OLEObj In .OLEObjects
If TypeOf OLEObj.Object Is MSForms.OptionButton Then
OLEObj.Object.GroupName = .Name
End If
Next OLEObj
End With

End Sub

 

[Guest]

I do have lots, a macro would probably do the trick, however I',m not sure how to get the macro into the GroupName in the properties box.

 

[Dave Peterson]

Actually, the macro would change the groupname of all the optionbuttons on a
sheet to that sheet's name.

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Short course:
Open your workbook
Hit alt-f11 to get to the VBE (where macros/UDF's live)
hit ctrl-R to view the project explorer
Find your workbook.
should look like: VBAProject (yourfilename.xls)

right click on the project name
Insert, then Module
You should see the code window pop up on the right hand side

Paste the code in there. Modify this line for the worksheet that you're fixing.

Set curWks = Worksheets("sheet1")

Now go back to excel and test it out--Tools|macro|macros...|Run

 

[Dave Peterson]

How about a manual method?

Show the Control toolbox toolbar
click on the Design mode icon

Show the drawing toolbar.
click on the arrow ("select object")

Lasso the first set of optionbuttons to be grouped.
Right click|Properties
change the groupname property to what you want for that group.

Finish the other 3 and then toggle the "select object" off and toggle the
"design mode" off.

If you really needed a macro, you could do something like this. But under bad
naming conventions, it could have problems, too.

Option Explicit
Sub testme01()

Dim OLEObj As OLEObject
Dim newWks As Worksheet
Dim curWks As Worksheet
Dim myGroupNames As Collection
Dim gCtr As Long

Set curWks = Worksheets("sheet1")

curWks.Copy _
after:=curWks

Set newWks = ActiveSheet

Set myGroupNames = New Collection

With newWks
On Error Resume Next
For Each OLEObj In .OLEObjects
If TypeOf OLEObj.Object Is MSForms.OptionButton Then
myGroupNames.Add _
OLEObj.Object.GroupName, CStr(OLEObj.Object.GroupName)
End If
Next OLEObj
On Error GoTo 0

For Each OLEObj In .OLEObjects
If TypeOf OLEObj.Object Is MSForms.OptionButton Then
For gCtr = 1 To myGroupNames.Count
If OLEObj.Object.GroupName = myGroupNames(gCtr) Then
OLEObj.Object.GroupName _
= .Name & "-" & Format(gCtr, "00")
Exit For
End If
Next gCtr
End If
Next OLEObj

End With

End Sub

==========
But you may find working with the optionbuttons from the Forms toolbar (along
with the "group box" icon on that Forms toolbar) easier.

That "group box" that you surround your option buttons makes it nice visually
for the user and easier for you.