If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
OptionButton Help
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?
|
#2
|
|||
|
|||
OptionButton Help
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 BryC wrote: 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 |
#3
|
|||
|
|||
OptionButton Help
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" wrote: 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 BryC wrote: 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 |
#4
|
|||
|
|||
OptionButton Help
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 BryC wrote: 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" wrote: 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 BryC wrote: 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 -- Dave Peterson |
#5
|
|||
|
|||
OptionButton Help
Thanks for the help. It did just what you said. My problem now is that I neglected to mention that in the worksheet to be copied I have four different sets of OptionButtons, so they can not all have the same name.
Is it possible to assign a slightly different name to the four sets? As far as I know random names or numbers won't work because the two OptionButtons in each set must have the same GroupName to work. And will the name change when I rename the sheets? By the way, I am new to macros and it seems a shame to have such a timesaving tool go to waste. I look forward to learning more about it and VBA after I complete this project. Again, thanks for the tips. -- Bryan C "Dave Peterson" wrote: 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 BryC wrote: 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" wrote: 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 BryC wrote: 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 -- Dave Peterson |
#6
|
|||
|
|||
OptionButton Help
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. BryC wrote: Thanks for the help. It did just what you said. My problem now is that I neglected to mention that in the worksheet to be copied I have four different sets of OptionButtons, so they can not all have the same name. Is it possible to assign a slightly different name to the four sets? As far as I know random names or numbers won't work because the two OptionButtons in each set must have the same GroupName to work. And will the name change when I rename the sheets? By the way, I am new to macros and it seems a shame to have such a timesaving tool go to waste. I look forward to learning more about it and VBA after I complete this project. Again, thanks for the tips. -- Bryan C "Dave Peterson" wrote: 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 BryC wrote: 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" wrote: 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 BryC wrote: 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 -- Dave Peterson -- Dave Peterson |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Setting LinkedCell of individual OptionButton | Angelito Cruz | Worksheet Functions | 3 | June 1st, 2004 09:25 PM |