get caption of optionbutton in userform from a cell in worksheet

[raj.bris]

Hi Experts

I have approximately 152 option buttions in 76 frames of a userform i.e. 2 optionbuttons in one frame and laid out as per the following example. I want to change the caption of all of them to what ever is sitting in range C14 to E89 of worksheet "Raj".
e.g. I want the following
In Frame1
Caption of OptionButton1 = C14
Caption of OptionButton2 = E14
.
In Frame2
Caption of OptionButton1 = C15
Caption of OptionButton2 = E15
.
.
In Frame76
Caption of OptionButton151 = C89
Caption of OptionButton152 = E89

By looking around I found the following code but it looks too complex for me and if someone can modify this to my needs and give me a brief explanation would really be app

Please Login or Register  to view this content.

one more thing my optionbutton are not renamed, they have the original name i.e. OptionButton1 and so on.

I do not want to make them invisible, if there is not text it should just be equal to "TBA".

Regards
Raj

[Norie]

Raj


You could use this to set the captions of all the option buttons.

Please Login or Register  to view this content.

Though I've got to ask, why do you have 76 frames?

[JBeaucaire]

With multiple pages in your form, I'm not sure its worth all this effort to create loops. It's longer code, but far simpler to "map" if you just put a list of optionbutton captions directly in the userform_initialize routine to adjust each button specifically. I know it's 152 lines of code, but that still seems easier to manage than the MANY loops you'll have to create to try an manage the different groups of buttons all pulling from the same sheet.

[Norie]

Jerry

Multiple pages? Did I miss something?

[raj.bris]

JBeaucaire........I am not sure about Multiple Pages in my form... please explain

Norie... thanks for the code... little problem there..

Because there are 76 pairs of option buttons. each pair goes in 1 Frame (I hope i explained why 76 frames).
your code is working but it is skipping cell value by one. i.e.

optionbutton1 = c15 and optionbutton2 = e15

optionbutton3=c17 and optionbuttton4=e17

optionbutton5=c19 and optionbutton6=e19

i guess we need to declare two variables one for optionbutton and one for cells, what do you think?

[Norie]

No need for 2 variables, just need to get things right.

Please Login or Register  to view this content.

PS Still wondering why so many frames - you don't need to put option buttons in a frame to group them.

[raj.bris]

Thanks Norie..

worked perfectly.. you guessed it right. Grouping two option buttons together was my reason behind putting that many frames. well I have done most of my designing part now, i guess it is too late. i have one more issue just came up.
i am trying to get a value for a label from a cell as well but it does not seem to do any thing.
the label is inside a frame may be that is the reason.

before I tried

Please Login or Register  to view this content.

that did not work then I tried the following

Please Login or Register  to view this content.

this did not work either. both are doing nothing.

Anything major I am doing wrong in this.

[Norie]

The 2nd one should work, where did you put the code?

I put the code for setting the option button captions in the userform's Initialize event.

PS You probaby know but you can use the GroupName property to group option/checkbox/toggle buttons.

[raj.bris]

Thanks in future I will keep in mind to use groupname properties for grouping optionbuttons etc.

but I have put the code in userform's initialize event.

Also, I tried your code to work on this issue too but no success. I did the following with your code

Please Login or Register  to view this content.

let me know if i need to check label properties or something.

[Norie]

Can you post the whole code and/or attach a sample workbook?

[raj.bris]

there is so much going on with the sheet. not sure if I should attach the whole sheet or

do you think it has got something to do with the other code in userform initialize event?
I am pasting whole code of initialize event. have a look please

Please Login or Register  to view this content.

[Norie]

The first thing you should do is get rid of this, it could just be hiding errors.

Please Login or Register  to view this content.

[raj.bris]

Hi Mate

sorry took me so long to get back...

I have put a comment on

Please Login or Register  to view this content.

now I am getting a
runtime error - 2147024809 (80070057)
Could not find the specified object

Any thoughts?

[Norie]

I think you need to check the names of the controls.

For example are the labels actually called Lable1, Lable2 etc?

[JosephP]

perhaps Label1 and Label2 etc? ;-)

[HankW501]

I see this has not yet been marked "SOLVED"; with what are you still having trouble?