ActiveX Option Buttons disappeared

[aaron_burr]

Hi,

Please see attached Workbook. I have created option buttons (Yes/No) in the additional record boxes - a total of 10 pairs but they have since disappeared but the code is still there!

I created them and it worked perfectly and then emailed the workbook to someone but then when i have come to re-open the original from my desktop and they have gone!

can anyone help?

[natefarm]

I feel your pain, my friend. You have encountered a frustrating Excel bug, which I ran into a number of years ago. The objects are still there, but they now have a width of 0, so you can't see them. To prove it, unhide all your columns. Put your sheet in Design mode, click on the PDF option button in row 31, and start hitting the Tab key. It will tab through the hidden objects (first the frame, then the yes/no options buttons within it, then the next frame, etc).

If I remember right, this problem started when we went from Office 97 to Office 2003. In my application, the worksheet consisted of a set of forms that were initially hidden by hiding all the rows. The user clicked buttons across the top to unhide the form(s) they wanted, but suddenly all the objects had disappeared. I finally figured out where they were (by tabbing), but found out quickly that manually resizing dozens of objects did no good because next time, they disappeared again.

If you can find a backup copy with the objects visible, start with that. My initial solution was to reverse the application design. I started with all the rows and objects visible. Then when they clicked a button, it hid all the other rows, and left the one they want visible. I also found that the problem only occurred when the app was closed and saved with the rows hidden. I could hide and unhide rows all I wanted and everything was fine, but if I saved it with hidden rows, then when I reopened and unhid them, the objects were flat. I suspect that this is why your emailed version appeared with the problem. Since your [Add Additional Record] buttons unhide a set of columns, I'm assuming you want all hidden to begin with, so the solution I used won't work for you.

Likely, a VBA solution will be needed. First, play around with the above info to find out if what I've said applies to you. Then if you need further assistance, let me know. If so, please attach a clean version of your app with all columns and objects visible. Fixing it won't be difficult if your problem is the same as mine was.

[aaron_burr]

Yes, your right, they are there when I tab through. And your correct that I need the columns hidden to start with so your initial solution will not work unfortunately.

I do need further assistance if you don't mind, my VBA knowledge is limited so a solution is out of my ability.

Attached is the completed and unhidden column/row version.

I appreciate your help.

[natefarm]

I looked through your columns and objects (frames and yes/no buttons) and observed a pattern from which I could create mathematical formulas to link the object names to the columns in which they should appear. With that, I created code that would move the objects to their correct location when you click the relative Add Additional Records buttons. In the process, I took the liberty of cleaning up some unnecessary code issues in your command button code, as well as in the code for your various option buttons, which should provide more efficiency and ease of future maintenance. It seemed to all work when I tried it. I hope it does so for you.

Buttons.xlsm

[aaron_burr]

Absolutely brilliant, I thank you good sir.

One thing.... there needs to be a button under 'Wish to receive remittance'. I have tried to add it in and decipher the code that stops it disappearing but have be unable to. I have attached the spreadsheet with the 2 extra buttons, could you be so kind and work you magic one last time so these 2 buttons are also permanent.

[natefarm]

Another potential problem is with the objects in row 18, so I included code for them as well. Replace the code for option buttons 3, 4, 9, and 10 with the following:

Please Login or Register  to view this content.

Then, I would also suggest some cleanup of remaining hidden objects left over from previous attempts. Switch to design mode, then select the "No" option button for "Any Multiple Products?". Hit Tab to jump to the next hidden object, and hit Delete. Repeat the above until all are deleted.

[LJMetzger]

@natefarm - Excellent analysis.

In my experience,' Active X' controls do not play well with others, especially when there are hidden rows and/or columns. Whenever I use 'Active X' controls, I have a routine that creates them all in VBA, and another routine to delete them all using VBA. This works great during software development.

In a production environment, before each save I put the state of Active X controls in Excel cells. For example I store:
a. Name
b. Top, Left, Width, Height
c. Visibility
I also store other important information such as which rows/columns are hidden. Then, I make all rows/columns visible. Then I save the workbook.

When the workbook opens the next time, the stored information is used to make the workbook appear as expected:
a. Restore 'Active X' controls to their proper state as required
b. Hide rows and columns as required

Usually all that needs to be done is to restore the correct height or width to ZERO value heights or widths.

It's a lot of work, but that's the price to pay for living with 'Active X'.

Lewis

[natefarm]

Interesting way to handle it. The solution I applied to my situation was different than the one I came up with for Aaron, and much simpler. My objects are all linked to cells, and are positioned directly on those cells, so all I need to do is loop through them and reposition to the cell location. This didn't work for Aaron because his objects weren't linked to cells. Here's the code:

Please Login or Register  to view this content.

[LJMetzger]

My objects are all linked to cells, and are positioned directly on those cells, so all I need to do is loop through them and reposition to the cell location.

My applications use a similar naming technique that includes the cell address. I usually use a Class event handler for 'Active X'. The cell address in the name makes it easy for the Event Handler to take appropriate action.

[Sgt Rock]

I am by no means an Excel Guru. However, I did find after searching and searching a possible answer to your question. I will reiterate, just to be clear for this happened to me. I wanted to use ActiveX
controls due to flexible formatting. I wanted to use ActiveX options - radio buttons. I wanted to group 5 related ActiveX radio buttons together - hence I found out about adding a Microsoft Frame for ActiveX controls = Developer > Insert > ActiveX controls > lower right pick more options > scroll down to Microsoft Frames 2.0 - select it and draw the desired size frame on your worksheet. Apparently, you just cannot simply insert an ActiveX option control or any other ActiveX control into that frame. YOU HAVE TO EDIT THAT FRAME AS I FOUND OUT - VIOLA! So, draw your frame the size you want. With the frame selected, right click on the frame, select Frame Object, select Edit. Guess what, a Tool box appears with your controls. Pick the one you want and now draw it in your Frame. Not sure if the attached image came through but this is what you should see. Now, when you leave design mode you'll still see your control button. Whew! this was an all morning affair, I hope I have saved some hours of Googling!
Happy Holidays to All!
MEW

2018-12-10_12-37-34.jpg