How to determine Frame Text and option button caption value

[welchs101]

Hi, See enclosed file.

I have several "frames" and in each "frame" are several radio option buttons.

What i want to do is determine the following:
For the radio button selected determine the frame and radio option button caption text.

For example: The file currently has the 2nd frame "Database2" and radio option button "Desgin Example" selected. So i would like to know what vba code could be used to determine those two text values........"Database2" and "Design Example".

Any ideas?

[mikerickson]

What kind of frames? on a userform or a worksheet.
If in a useform, code like this might work

Please Login or Register  to view this content.

[welchs101]

its not on a userform.....i think its on a worksheet.

---------- Post added at 03:02 PM ---------- Previous post was at 02:53 PM ----------

i tried your code......getting a runtime error ...gives me invalide use of keyword "Me"

[mikerickson]

ActiveX controls? My Mac doesn't support ActiveX, but as I recall, the properties are similar to those of userform controls.

----

Me refers to the Useform. As a blind guess, try changing to ActiveSheet.

[welchs101]

i changed your code to the following

Please Login or Register  to view this content.

but when i try to run this i get an error at this ste

Please Login or Register  to view this content.

[mikerickson]

I don't know ActiveX controls. What error do you get? When you debug what does the immediate window give you for AControl.Name?

[nilem]

Hi welchs101, try it

Please Login or Register  to view this content.

[welchs101]

nilem,

I tried your excel file and it worked.......but when i put your code into my file i get the correct "option button caption" but not the correct "frame text". see enclosed file.......not sure why the frame text does not work.........any ideas?

[welchs101]

so i think ......not sure........but instead of "frame text" i think what i am looking for is the groupbox text.........

[nilem]

two options, see the attachment

[welchs101]

hi nilem,

thanks for you help. really appreaciated. I learned a few things. ONe thing i learned is that what i want to do i dont think can be done the way i was thinking.

I have several "boxes" on a single worksheet. In each box there are several radio buttons. On the entire sheet the user can select only a single button...........the group name parameter in the properties window is the same for ALL the radio buttons on the sheet.........

what i was hoping to do is capture the radio button text (which you showed me how to do) and the "box title text" which i dont think is possible from what i can tell.

But thanks a lot for your help.

[nilem]

You can use an array of titles, something like
box1 - Button1
box1 - Button2
box2 - button1
box2 - Button2
box2 - Button3 etc.
Received the name of a button, you can get the name of box from the array.
You can also try to use linked cells on a worksheet, or drop-down lists, or ... many options.

[JosephP]

if you really want it with your layout (I think nilem's second option was better)

Please Login or Register  to view this content.

[welchs101]

thanks nilem and joseph.

joseph, i tried the code you sent.....it worked. Nilem thanks for your help as well. Both very much appreciated. I learned some stuff .......thanks.

[welchs101]

josephP,

I reviewed your code.........to be honest i dotn understand it all......can you explain this code snipet.....

Please Login or Register  to view this content.

not sure i understand why this works.

[JosephP]

translation-
is the cell under the top left corner of the option button within the range of cells that the groupbox is over?

[welchs101]

hi josephP,

how on earth did you ever think of this......i would have never thought of doing something like that........

always something to learn i guess.

so you can determine the cell under the option button........any good links on this?

[mikerickson]

The ObjectBrower in the VB editor is a powerful aid.

[welchs101]

OK......its not working............i changed the layout and not my code does not work..........

What is supposed to happen is that the user selects an option button. My code then retrieves the option button value and then determines what "Frame" or "group" this option button came from.

My code is not retrievign the "Frame" or "group" name correctly. I had used some code that was working but i did not read the comments carefully enough.......it works for only a specific layout..............now for example if user selects (see enclosed file)the option button "Internal Trainings" under the "General activities" frame/group what is returned is
Optionbutton value = Internal Trainings
Caption/group name value = "Data Import/Export" which is the group name to the left.....


Any ideas on how to either redo or fix this would be greatly appreaciated..........found this error out at a really bad time......guess murphy's law is in play.

[JosephP]

you have overlap. the data import/export frame extends into column g (barely) so the top left cell of the option button (g25) is within the range specified by the top left cell of the frame (a20) and its bottom right cell (g28). you need to make sure there is no overlap

[welchs101]

joseph,

So if i just keep the left most boxes limited to column F and then the right most boxes starting in col-g then things seem to work.....................

THANKS!!!!!!!!!!!!!!!!!

that seems to fix it...........

you have no idea how thankful i am

[JosephP]

you're welcome :-)

If you are satisfied with the solution(s) provided, please mark your thread as Solved.


New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save