Create multiple Option Button Groups 1

[calvinb]

I need to create many mutually exclusive option buttons and it's very difficult and time consuming doing it by hand!

I have 20 rows where I need a group of 3 in columns J4:L4, then a group of 2 in columns M4:N4, then another group of 3 in columns O4:Q4 and finally a group of 2 in columns U4:V4.

I am using Excel 2010 and Form controls, not Active X. The linked cells need to start 17 cells to the right of column J4 which is column AA4.

If someone could show me how to do this, I would be very thankful!!

 

[SkipVought]

hi,

Turn on your macro recorder and record adding ONE OptionBox and ONE GroupBox.

Post back with your recorded code.

What 20 rows: 4 to 23?

On a separate sheet, make a table to define your groups and give each group a name, like
[tt]
GroupName FromCol ToCol
Group1 J L
Group2 M N
...
[/tt]
NAME the ranges in your table, using the Names in Row 1.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[calvinb]

Thanks Skip. Here is the code I get when I create an option button and a group:

Sub Macro1()
'
' Macro1 This is the default sizes when I added an option button and a group.
'

'
    ActiveSheet.OptionButtons.Add(370.5, 32.25, 72, 72).Select
    ActiveSheet.GroupBoxes.Add(478.5, 34.5, 72, 72).Select
End Sub

Those are just the default sizes. I need them to fit the rows and don't need any titles.

I'm using rows 4 to 23.

Group Name From Col To Col Range Name
Group1 J L Group1
Group2 M N Group2
Group3 O Q Group3
Group4 U V Group4

I am attaching a copy of the actual worksheet I want the option buttons on. You will see I already have checkboxes there but I can't figure out how to make them mutually exclusive in the above groups. From my research on the Internet, it seems the only way is to use Option Buttons. If it would be easier to make the checkboxes mutually exclusive, I am fine with that. As long as it works I don't care if they are checkboxes or option buttons.

Thanks for your help, Skip!!

PS I've never uploaded before so I hope it worked!! Seems like it did.

 

[SkipVought]

CheckBoxes are not made to be mutually exclusive like OptionButtons so don't try.

I don't use MSForms much but I recall adding the GroupBox first and then placing the OptionButtons INSIDE the GroupBox in order to have those controls work together.

Also you have controls on rows that are unused. This is not a very sound design. If you want to do this by hand, then just go ahead and set it up like a 20th century paper form. Print it out and use paper and pencil. I would place the controls via code as DATA is entered into the TABLE. Use a Structured Table, new to 2007. I'd have to check but it may even be possible that the controls may propagate to new row of data as they are added. Look at Excel HELP on Structured Table -- GOOD STUFF!

I can't help anymore today as I am leaving.

good luck!

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[calvinb]

Thanks Skip!

I removed the other lines and just left 3 for demonstration purposes.

The way this works is that we have a table of information online that I copy and paste on the sheet called "Import". (I would like to then check that for duplicates and have them move to the top rows as duplicates have to be processed differently). Then we print that off for reference.

Next I copy 20 rows at a time and paste it in the sheet called "Data". Then we have go online for each item and check the corresponding option button for each group.

On the sheet called "Form" there is a form which is populated from the Data sheet. When I click the "Print the Control Sheets" button, a form is printed for each row in the "Data" sheet.

Then I delete those 20 and copy and print 20 more until finished.

I'm sure there is a much more efficient way to do this but my Excel knowledge is very limited so this is the best I could come up with... lol

 

[calvinb]

Ok, I did it manually!! It took all afternoon but I entered the option buttons in individual groupboxes for each row and each group and it is all working fine.

Thanks for your efforts, Skip!

PS I still want to be able to find duplicate entrys and move them to the beginning of the list using VBA so I'll probably be posting again soon!

 

[SkipVought]

how do you define a duplicate entry? You may be able to use a COUNTIFS() function.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]