option button array?

[CPerdue]

Hi all,

I'm trying to design a table for data entry. Line items (outputs) are pre-populated, and it is up to the user to specify which, if any, function they perform by choosing selection objects from various columns.

Depending on other data, I want to control what the user is able to select.
Case 1, only one selection per column, only one selection per row.
Case 2, multiple selections per column, only one per row.
Case 3, freeform, select all you like.

I know the option buttons allow grouping to restrict choices to one per group. I could group as rows for case 2 but I don't see a way to use the group properties for the other cases. I think I will have to use un-grouped buttons (or check-boxes) and do the housekeeping myself. Suggestions?



Thanks,
Clint

 

[CPerdue]

Here is a borders-cut-paste example...I put it here because it is ugly for some reason. All the "OB"s are option buttons.


<TABLE style="WIDTH: 275pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=366><COLGROUP><COL style="WIDTH: 141pt; mso-width-source: userset; mso-width-alt: 6875" width=188><COL style="WIDTH: 67pt; mso-width-source: userset; mso-width-alt: 3254" span=2 width=89><TBODY><TR style="HEIGHT: 24.95pt; mso-height-source: userset" height=33><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 141pt; HEIGHT: 24.95pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=33 vAlign=top width=188 align=left><TABLE cellSpacing=0 cellPadding=0><TBODY><TR><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; WIDTH: 141pt; HEIGHT: 24.95pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 1pt solid" class=xl80 height=33 width=188>Choose actions </TD></TR></TBODY></TABLE></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 67pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl81 rowSpan=2 width=89>FORWARD</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 67pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl83 rowSpan=2 width=89>REVERSE</TD></TR><TR style="HEIGHT: 24.95pt; mso-height-source: userset" height=33><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; WIDTH: 141pt; HEIGHT: 24.95pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl71 height=33 width=188>SUGGESTED OUTPUTS</TD></TR><TR style="HEIGHT: 24.95pt; mso-height-source: userset" height=33><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 24.95pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl72 height=33>Output-1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 67pt; HEIGHT: 24.95pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl73 height=33 width=89><?xml:namespace prefix = v ns = "urn:schemas-microsoft-com:vml" /><v:shapetype id=_x0000_t201 path="m,l,21600r21600,l21600,xe" o:spt="201" coordsize="21600,21600"><v:stroke joinstyle="miter"></v:stroke><vath o:connecttype="rect" o:extrusionok="f" fillok="f" strokeok="f" shadowok="f"></vath><?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o:lock v:ext="edit" shapetype="t"></o:lock></v:shapetype><v:shape style="Z-INDEX: 8; POSITION: absolute; MARGIN-TOP: 3pt; WIDTH: 57pt; HEIGHT: 19.5pt; MARGIN-LEFT: 6pt" id=OB1_1 stroked="f" o:insetmode="auto" type="#_x0000_t201" o:spid="_x0000_s12294" strokecolor="windowText [64]" o:regroupid="2"><v:imagedata o:title="" src="file:///C:\Users\501504~1\AppData\Local\Temp\msohtmlclip1\01\clip_image003.emz"></v:imagedata><?xml:namespace prefix = x ns = "urn:schemas-microsoft-comffice:excel" /><x:ClientData ObjectType="Pict"><x:AutoLine>OB1_1</x:AutoLine></x:ClientData></v:shape></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 67pt; HEIGHT: 24.95pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 1pt solid" class=xl74 height=33 width=89><v:shape style="Z-INDEX: 9; POSITION: absolute; MARGIN-TOP: 3pt; WIDTH: 55.5pt; HEIGHT: 19.5pt; MARGIN-LEFT: 7.5pt" id=OB1_2 stroked="f" o:insetmode="auto" type="#_x0000_t201" o:spid="_x0000_s12295" strokecolor="windowText [64]" o:regroupid="2"><x:ClientData ObjectType="Pict">OB1_2 <x:AutoPict></x:AutoPict><x:MapOCX></x:MapOCX></x:ClientData></v:shape></TD></TR><TR style="HEIGHT: 24.95pt; mso-height-source: userset" height=33><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 24.95pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl75 height=33>Output-2</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 67pt; HEIGHT: 24.95pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl70 height=33 width=89><v:shape style="Z-INDEX: 10; POSITION: absolute; MARGIN-TOP: 2.25pt; WIDTH: 57pt; HEIGHT: 19.5pt; MARGIN-LEFT: 6pt" id=OB2_1 stroked="f" o:insetmode="auto" type="#_x0000_t201" o:spid="_x0000_s12299" strokecolor="windowText [64]" o:regroupid="1"><x:ClientData ObjectType="Pict">OB2_1 <x:AutoPict></x:AutoPict><x:MapOCX></x:MapOCX></x:ClientData></v:shape></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 67pt; HEIGHT: 24.95pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl76 height=33 width=89><v:shape style="Z-INDEX: 11; POSITION: absolute; MARGIN-TOP: 2.25pt; WIDTH: 55.5pt; HEIGHT: 19.5pt; MARGIN-LEFT: 7.5pt" id=OB2_2 stroked="f" o:insetmode="auto" type="#_x0000_t201" o:spid="_x0000_s12300" strokecolor="windowText [64]" o:regroupid="1"><v:imagedata o:title="" src="file:///C:\Users\501504~1\AppData\Local\Temp\msohtmlclip1\01\clip_image009.emz"></v:imagedata><x:ClientData ObjectType="Pict"><x:AutoLine>OB2_2</x:AutoLine></x:ClientData></v:shape></TD></TR><TR style="HEIGHT: 24.95pt; mso-height-source: userset" height=33><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 24.95pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl75 height=33>Output-3</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 67pt; HEIGHT: 24.95pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl70 height=33 width=89><v:shape style="Z-INDEX: 12; POSITION: absolute; MARGIN-TOP: 3pt; WIDTH: 57pt; HEIGHT: 19.5pt; MARGIN-LEFT: 6pt" id=OB3_1 stroked="f" o:insetmode="auto" type="#_x0000_t201" o:spid="_x0000_s12302" strokecolor="windowText [64]" o:regroupid="3"><v:imagedata o:title="" src="file:///C:\Users\501504~1\AppData\Local\Temp\msohtmlclip1\01\clip_image011.emz"></v:imagedata><x:ClientData ObjectType="Pict"><x:AutoLine>OB3_1</x:AutoLine> <x:AutoPict></x:AutoPict><x:MapOCX></x:MapOCX></x:ClientData></v:shape></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 67pt; HEIGHT: 24.95pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl76 height=33 width=89><v:shape style="Z-INDEX: 13; POSITION: absolute; MARGIN-TOP: 2.25pt; WIDTH: 55.5pt; HEIGHT: 20.25pt; MARGIN-LEFT: 7.5pt" id=OB3_2 stroked="f" o:insetmode="auto" type="#_x0000_t201" o:spid="_x0000_s12303" strokecolor="windowText [64]" o:regroupid="3"><x:ClientData ObjectType="Pict"><x:AutoPict></x:AutoPict><x:MapOCX>OB3_2</x:MapOCX></x:ClientData></v:shape></TD></TR><TR style="HEIGHT: 24.95pt; mso-height-source: userset" height=33><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 24.95pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl75 height=33></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 67pt; HEIGHT: 24.95pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl70 height=33 width=89><v:shape style="Z-INDEX: 14; POSITION: absolute; MARGIN-TOP: 3pt; WIDTH: 57pt; HEIGHT: 19.5pt; MARGIN-LEFT: 6pt" id=OB4_1 stroked="f" o:insetmode="auto" type="#_x0000_t201" o:spid="_x0000_s12304" strokecolor="windowText [64]"><x:ClientData ObjectType="Pict"><x:AutoPict></x:AutoPict><x:MapOCX>OB4_1</x:MapOCX></x:ClientData></v:shape></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 67pt; HEIGHT: 24.95pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl76 height=33 width=89><v:shape style="Z-INDEX: 15; POSITION: absolute; MARGIN-TOP: 3pt; WIDTH: 55.5pt; HEIGHT: 19.5pt; MARGIN-LEFT: 7.5pt" id=OB4_2 stroked="f" o:insetmode="auto" type="#_x0000_t201" o:spid="_x0000_s12305" strokecolor="windowText [64]"><x:ClientData ObjectType="Pict"><x:AutoPict></x:AutoPict><x:MapOCX>OB4_2</x:MapOCX></x:ClientData></v:shape></TD></TR><TR style="HEIGHT: 24.95pt; mso-height-source: userset" height=33><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 24.95pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl75 height=33></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 67pt; HEIGHT: 24.95pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl70 height=33 width=89><v:shape style="Z-INDEX: 16; POSITION: absolute; MARGIN-TOP: 2.25pt; WIDTH: 57pt; HEIGHT: 19.5pt; MARGIN-LEFT: 6pt" id=OB5_1 stroked="f" o:insetmode="auto" type="#_x0000_t201" o:spid="_x0000_s12306" strokecolor="windowText [64]"><x:ClientData ObjectType="Pict"><x:AutoPict></x:AutoPict><x:MapOCX>OB5_1</x:MapOCX></x:ClientData></v:shape></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 67pt; HEIGHT: 24.95pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl76 height=33 width=89><v:shape style="Z-INDEX: 17; POSITION: absolute; MARGIN-TOP: 2.25pt; WIDTH: 55.5pt; HEIGHT: 19.5pt; MARGIN-LEFT: 7.5pt" id=OB5_2 stroked="f" o:insetmode="auto" type="#_x0000_t201" o:spid="_x0000_s12307" strokecolor="windowText [64]"><x:ClientData ObjectType="Pict"><x:AutoPict></x:AutoPict><x:MapOCX>OB5_2</x:MapOCX></x:ClientData></v:shape></TD></TR><TR style="HEIGHT: 24.95pt; mso-height-source: userset" height=33><TD style="BORDER-BOTTOM: windowtext 2pt double; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 24.95pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl77 height=33></TD><TD style="BORDER-BOTTOM: windowtext 2pt double; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 67pt; HEIGHT: 24.95pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl78 height=33 width=89><v:shape style="Z-INDEX: 18; POSITION: absolute; MARGIN-TOP: 3pt; WIDTH: 57pt; HEIGHT: 19.5pt; MARGIN-LEFT: 6pt" id=OB6_1 stroked="f" o:insetmode="auto" type="#_x0000_t201" o:spid="_x0000_s12308" strokecolor="windowText [64]"><x:ClientData ObjectType="Pict"><x:AutoPict></x:AutoPict><x:MapOCX>OB6_1</x:MapOCX></x:ClientData></v:shape></TD><TD style="BORDER-BOTTOM: windowtext 2pt double; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 67pt; HEIGHT: 24.95pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl79 height=33 width=89><v:shape style="Z-INDEX: 19; POSITION: absolute; MARGIN-TOP: 2.25pt; WIDTH: 55.5pt; HEIGHT: 20.25pt; MARGIN-LEFT: 7.5pt" id=OB6_2 stroked="f" o:insetmode="auto" type="#_x0000_t201" o:spid="_x0000_s12309" strokecolor="windowText [64]"><x:ClientData ObjectType="Pict"><x:MapOCX>OB6_2</x:MapOCX></x:ClientData></v:shape></TD></TR></TBODY></TABLE>

 

[sous2817]

I think you're better off using checkboxes and managing the rules through code. Option buttons are either "on" or "off" and you can only have one selected per group, so you wouldn't be able to do "Case 3" in your example above.

 

[mikerickson]

Perhaps something like this pseudo-code.

Dim oneButton as Object
Dim grpName as String

For each oneButton in OptionButtons
    Select Case actionType
        Case "by Columns"
            grpName = "grp" & Right(oneButton.Name, 1)
        Case "by Rows"
            grpName = Left(oneButton.Name, 3"
        Case "Free for all"
            grpName = oneButton.Name
    End Select

    oneButton.GroupName = grpName
Next oneButton

 

[CPerdue]

Point taken. I have seen all options false when the sheet activates, I figured there was a way to initialize the choices on demand but haven't gotten there yet...

Thanks,

C.

 

[CPerdue]

I'll give the cases route a try, thanks.

C.

 

[sous2817]

Welcome to the board! Just saw you're in Roanoke, VA. I lived in Christiansburg and worked in Roanoke (for Carilion) for a few years...small world.....

I never thought about doing it mikerickson's way. Seems like it'll do exactly what you want. Post back if you're having troubles and I'll see if I can help at all.

Best of luck!

 

[CPerdue]

You are too kind. Yeah, this area is hard to leave...been around the world and always come back.

I'll post whatever I end up with.

C.

 

[CPerdue]

Phase II,
Once I have a selection that is true, how do I know where it is so that I can grab data from another cell on the same line? I guess I could link to a cell on the intended line and drop a flag in it. I'd really like the address of the cell beneath the control (bullettproof against adding rows, moving the table, etc. of course).

 

[sous2817]

There are two properties that you can use to get a controls position on the sheet:

.BottomRightCell.Address
.TopLeftCell.Address

To get a feel for how it works, try something like (adjust sheet name, and optionbutton name to suit):

Sub test()
MsgBox Sheet1.OptionButton1.BottomRightCell.Address
MsgBox Sheet1.OptionButton1.TopLeftCell.Address
End Sub