option button array?

CPerdue

New Member
Joined
May 6, 2011
Messages
27
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
 
Hmm. OK, what about going the other way...say I am scanning through a column with Range("name")(pointer), and I want to interrogate the object in an adjoining column. Even if I have named the objects as ("CheckBox_" & (pointer)), I can't seem to interrogate an object with a name string made from a variable, nor retrieve something from ActiveSheet.OLEObjects by its Name property. Is this a compile time vs. runtime issue?

All I have been able to do is loop through all the OLEObjects on the sheet and pick out the one which matches the name I want as it goes by. Inefficient!!

Thanks again,
C.
 
Upvote 0

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
Considering scrapping the option buttons and using Marlett checkboxes. They are on the same layer as the cells (they are cells), and easy to implement with any grouping scheme you like.

You can find examples on the forum, or I can post some code.
 
Upvote 0
OK, here is the 'front end' of what works...I extract meaningful parts of the object name which I use to drive choices about what to do with it's value later on.

Code:
        For Each oleTemp In ActiveSheet.OLEObjects  ' loop through all controls
            
            If TypeName(oleTemp.Object) = "CheckBox" And oleTemp.Object.Value = True Then   ' find only selected checkboxes
                'decode box name (I,O)CB(index 1-n)_(direction 1,2)
                boxName = oleTemp.Name
                IO_type = Left(boxName, 1)
                index = Mid(boxName, 4, 1)
                direction = Right(boxName, 1)

this did NOT work

Code:
dim temp as string
dim x
 
for i = 1 to 10
    temp = "CheckBox_" & i
    x = temp.value
...
 
next i
 
Upvote 0
Bueno.

I'll bet you never put another checkbox or option button on a sheet.
 
Upvote 0

Forum statistics

Threads
1,224,603
Messages
6,179,849
Members
452,948
Latest member
UsmanAli786

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top