Macro "hangs" when clicking radio button

[dflak]

I am building a spreadsheet that generates a survey checklist. It produces the sheet properly, but when I click on one of the radio buttons, the workbook becomes non-responsive. I click the X to close the workbook and get the options, to wait for the program to respond, close the workbook or re-open the workbook. If I choose close or reopen, I get a notice that there isn't enough memory, when I OK it, it proceeds.

When I reopen the workbook, the survey is still there and now the radio buttons work. However column A has now shrunk back down from a width of 100.

So there is something "fishy" about the group with the radio buttons.

[6StringJazzer]

I was able to reproduce your problem, although I don't think it's a macro hanging because you do not have the buttons associated with macros. I am guessing there is some kind of corruption or exotic Excel bug. One thing I would try is to replace the Forms option buttons with ActiveX option buttons.

[dflak]

I thought about that but Active X does not have a Group Box control. How do I "corral" the radio buttons into groups?

[dflak]

From a practical standpoint, I can make this a "combo box" by generating a list for data validation like I do with the Yes/No. In some ways this is better in that it's more flexible and the user can provide as many choices as are needed and use custom verbiage. Also it will produce a more consistent user interface.

However, from an "academic" standpoint I would like to get this to work. It might come in handy sometime in the future.

[6StringJazzer]

I thought about that but Active X does not have a Group Box control. How do I "corral" the radio buttons into groups?

If you only want one group, they will be in the same group by default if they are on the same sheet.

If you need more than one group, Go to the Developer panel on the Ribbon and click Design. Right-click on an option button and select Properties. In the Group property give each group a unique name.

[dflak]

Thanks. Got it! I tried an experiment. I simply Grouped them and they seemed to work. Now I need to copy multiple of them to see if they get assigned new group names when they get pasted. I suspect they will.

[dflak]

The copy / paste does create new group names : new SHEET group names which apparently have nothing to do with Active X group names.

Alas the copy / paste makes "clones" of the Active X group. I tried recording a macro but the macro recorder can't see the other side of the Design Mode. So I am still searching to see if there is a way to set an ActiveX Group name in VBA. I'm looking to copy / paste and then somehow identify the buttons and change their groups. So far I've only looked at about two dozen articles. .

[dflak]

I will close this question and ask the more pointed question How do I set a group for an option button using VBA.

[6StringJazzer]

In VBA you can directly address the same properties that you can see in Design mode:

Please Login or Register  to view this content.

[dflak]

Thanks for the tip, but I am not getting this far. I have the same issue with Active X buttons as I do with form buttons. The program hangs.

I have an ancient program that produced a test with radio buttons. I am going to see if I can find it and see if I am doing something differently there. I think in that case, I am producing the buttons and not copying them in.

I'll report back if I can find it and make it work.

[nigelog]

@dflak
have a look at attached. I grouped these radio buttons by adding to a class. I wanted the tag value to give me the hose diameter chosen to provide comboboxes of only the fittings that were relevant to the hose size. The Public variables have to be declared in the first module rather than the class module. You may find a pointer in the code to help

Please Login or Register  to view this content.

Please Login or Register  to view this content.

[dflak]

Found it and adapted it. I still need some cleanup and I want to make it more flexible so people can select different ranges with different (user-defined) captions and a different number of radio buttons.

But the concept works and the worksheet does not hang.

Eventually I'll copy the survey sheet into a workbook to distribute. When the users return the workbooks, this workbook will have a macro to read them and append the answers to a database in this workbook. That macro will have to read the definitions table to figure out how to parse out the answers.

Thanks again for your help. Although I could not use the suggestions, I now know some things I didn't know before and I can see where these things can be useful in the future.

[dflak]

@nigelog, your post came in as I was posting. I'll have to take some time to digest the code you posted.

I can see that it is working with User Forms. I'll have to see if I can adapt that to sheets.