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]

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.

 

[sous2817]

Can you post your code?

 

[shg]

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.

 

[sous2817]

I agree with shg. Have a look here, I've used it before and it's worked quite well:

http://www.vbaexpress.com/kb/getarticle.php?kb_id=879

 

[CPerdue]

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.

        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

dim temp as string
dim x
 
for i = 1 to 10
    temp = "CheckBox_" & i
    x = temp.value
...
 
next i

 

[sous2817]

are you using ActiveX or a Form option button?

 

[CPerdue]

I'm searching for "Marlett checkboxes", a quick link would be appreciated.

 

[CPerdue]

ActiveX

 

[CPerdue]

http://www.vbaexpress.com/kb/getarticle.php?kb_id=879

 

[shg]

Bueno.

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